Merge two macros of 2 diffrent workbooks [Solved/Closed]

Report
-
 rash -
Hello,

I have 2 macros in 2 different workbook

Currently i have to run 1 st macro(!st work book),then open 2nd macro workbook then have to run 2nd macro

Is there any solution where i can link both the macros???


Thanks



3 replies

Hi Trowa

macro codes are as follows









Macro1

Sub PrdctData_RmvDupe()
Dim rngPrdData As Range

Dim lngC As Long
Dim shtNew As Worksheet

wksProductionData.AutoFilterMode = False

lngC = Application.WorksheetFunction.CountA(wksProductionData.Range("A1:A10000"))
Set rngPrdData = wksProductionData.Range("A1").Resize(lngC, 11)

'Remove Duplicates
rngPrdData.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11), Header:=xlYes

'Replace Strings
lngC = Application.WorksheetFunction.CountA(wksProductionData.Range("A2:A10000"))
wksProductionData.Range("V2").Resize(lngC, 1).Value = "=VLOOKUP(I2,wksMenu!$E$11:$F$1000,2,0)"

wksProductionData.Range("I2").Resize(lngC, 1).Value = wksProductionData.Range("V2").Resize(lngC, 1).Value
wksProductionData.Range("V:V").ClearContents

'Remove Duplicates
rngPrdData.RemoveDuplicates Columns:=Array(1, 5, 6, 9), Header:=xlYes

' 'Insert New Sheet
' Set shtNew = ThisWorkbook.Sheets.Add
' shtNew.Name = "NAP"


rngPrdData.AutoFilter 2, "Website"
rngPrdData.AutoFilter 9, "Resolved"
rngPrdData.SpecialCells(xlCellTypeVisible).Copy
wksNAP.Range("A1").PasteSpecial

lngC = Application.WorksheetFunction.CountA(wksNAP.Range("A2:A10000"))
wksNAP.Range("L1").Value = "Act/Non"
wksNAP.Range("L2").Resize(lngC, 1).Value = "=IF(LEFT(K2,6)=""DefCon"",""ACT"",""Non"")"

'Data for Pivot
Set rngPrdData = wksNAP.Range("A1").CurrentRegion

wksPivot.PivotTables("PivotTable1").ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=rngPrdData)

'Ensure Pivot Table is Refreshed
wksPivot.PivotTables("PivotTable1").RefreshTable

lngC = Application.WorksheetFunction.CountA(wksPivot.Range("A5:A10000"))

wksPivot.Range("J5:L10000").ClearContents

wksPivot.Range("A5").Resize(lngC, 1).Copy
wksPivot.Range("J5").PasteSpecial

wksPivot.Range("C5").Resize(lngC, 1).Copy
wksPivot.Range("K5").PasteSpecial

wksPivot.Range("L5").Resize(lngC, 1).Value = "=K5/D5"
wksPivot.Range("L5").Resize(lngC, 1).Value = wksPivot.Range("L5").Resize(lngC, 1).Value

End Sub



Macro2

Module1



Sub Report()
'
Application.ScreenUpdating = False
Sheets("production data").Select
Range("K1") = "MP"
Range("L1") = "PL"
Range("M1") = "SLA Status"
Range("n1") = "Vendorcorrect/incorrect"
Range("o1") = "Task"


Range("k2") = "=LEFT(VLOOKUP(G2,'DE FR GB RGs'!O:P,2,FALSE),2)"
Range("l2") = "=RIGHT(VLOOKUP(G2,'DE FR GB RGs'!O:P,2,FALSE),2)"
Range("m2") = "=IF(I2=""Resolved"",IF(AND(O2=""Iss"",H2-E2<=14),""Within SLA"",IF(OR(AND(O2=""Andon"",H2-E2<=7),AND(O2=""CS"",H2-E2<=7)),""Within SLA"",""Out of SLA"")),"""")"
Range("n2") = "=IF(LEN(J2)=5,""Correct"",""Incorrect"")"
Range("o2") = "=IF(B2=""SCOS"",""CS"",IF(RIGHT(C2,4)=""Cord"",""Andon"",""Iss""))"
Range("a100000").Select
Selection.End(xlUp).Offset(0, 10).Select
Range(Selection, Selection.Offset(0, 4)).Select
Range(Selection, Range("K2")).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False

Call sort
Sheets("production pivot").Select
Range("B7").Select
ActiveWorkbook.RefreshAll
Sheets("production data").Select



Columns("m:o").Select
Selection.Clear
Cells(1).Select

Application.OnKey "%~", "report"


End Sub










Module2



Sub sort()
'
' sort Macro
'

'
Sheets("Production Data").Select
Range("K1").Select
Selection.AutoFilter
Range("K1").Select
ActiveWorkbook.Worksheets("Production Data").AutoFilter.sort.SortFields.Clear
ActiveWorkbook.Worksheets("Production Data").AutoFilter.sort.SortFields.Add _
Key:=Range("K:K"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Production Data").AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("K1").Select
ActiveSheet.Range("$A:$O").AutoFilter Field:=11, Criteria1:="#N/A"


Range("A2").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Cells(1).Select
Selection.AutoFilter

End Sub



Regards
RASH
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
without know where it errors out, here is few things that you can look into.

In your macro PrdctData_RmvDupe, you have reference to three worksheets
1. wksProductionData
2. wksNAP
3. wksPivot

These are not defined in the code, so either they have been declared some where else (which i think is the case), or are the actual sheet name (which do not think is the case)

So if you paste only PrdctData_RmvDupe in your other workbook it will not work as these variables are not known

On the other hand in your other macro, "Report" (as well as "sort"), you refer to a sheet "Production Data".
So if you copy Report and sort to other workbook, it will not work unless you have sheet Production Data in that workbook as well. Also you have been making reference to
ActiveWorkbook.Worksheets("Production Data").

From what i see, it seems to me that you would like to keep macro in separate workbook but don't want to manually open both

For that path of least resistance would be to move "Report" and "sort" to first workbook.

So before invoking Report, you can add line to open the other work book and then call report
Hi

Thank you so much for help
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Rash,

Are they event macro's?

You could consider posting both codes, for us to see if we can help.

Looking through other peoples codes isn't one of my strong points, but I can give it a try.

Best regards,
Trowa
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Rash,

What is stopping you from putting both macro's in the same workbook?

Best regards,
Trowa
Hi Trowa,

I'm getting run time error when i'm trying to run it.out of 2 macro one is wriiten by me and 1 more is by my senior,so i dont know the logic of second macro.

Is there any way where we can link macros of 2 different workbook???


Regards,
Rash

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!