Previous Topic Next Topic

Merge two macros of 2 diffrent workbooks

Solved/Closed
rash - Nov 2, 2015 at 01:26 AM
 rash - Nov 7, 2015 at 12:33 PM
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



Related:

3 responses

Answer 1 / 3
rash
Nov 6, 2015 at 12:06 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 6, 2015 at 10:33 AM
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
0
rash
Nov 7, 2015 at 12:33 PM
Hi

Thank you so much for help
0
Answer 2 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 3, 2015 at 11:00 AM
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
1
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 2, 2015 at 12:13 PM
Hi Rash,

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

Best regards,
Trowa
0
rash
Nov 2, 2015 at 10:38 PM
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
0