replacing as variables in sort,subtotal macros

Mr. Hopkins

I got it because you led the way.

Thank you for your guidance.

more power

Mr. Hopkins

I've been really trying to solve the SUB for some days now... 

and after so many trials & errors - I finally got it.. pls see the code below.

( a simple declaration of variables to PUBLIC)

Thank you for your time.

Public mWB As Workbook, targetedWB As Workbook   '**** this make it work!!

Sub Compile_Synopsis()
   
    Set mWB = ActiveWorkbook
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim strFileToOpen As String
    strFileToOpen = Application.GetOpenFilename _
        (Title:="Please choose a file to open", FileFilter:="Excel Files *.xls* (*.xls*),")
    Workbooks.Open Filename:=strFileToOpen
    Set targetedWB = Workbooks.Open(strFileToOpen)
    Dim LastRow As Long
    Call Copy_synopsis("B", "B2:C", "B2")   
    Call Copy_synopsis("D", "D2:E", "D2")   
    Call Copy_synopsis("F", "F2:G", "F2")
    Call Copy_synopsis("H", "H2:I", "H2")
    Application.ScreenUpdating = True
    targetedWB.Close SaveChanges:=False
    On Error GoTo 0
  
End Sub

Sub Copy_synopsis(mcol As String, mrng As String, mcel As String)

    targetedWB.Activate   
    Sheets("synopsis").Select
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, mcol).End(xlUp).Row
    End With
    Range(mrng & LastRow).Select
    Selection.Copy
    mWB.Activate
    Range(mcel).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                :=False, Transpose:=False

End Sub

Mr. Hopkins

Hope you are well and good.

pardon my bothering again..

In conjunction with the above scenario, I tried applying the procedure to the codes below - but it is not working. I suspect it to be the workbook activation or the global declaration or something else.

your kind assistance would be appreciated very much.

many many many thanks

What the SUB is doing is - it is copying data from sheet "synopsis" of another workbook to the active workbook mWB

Sub Compile_Synopsis()

    Dim mWB As Workbook       
    Dim targetedWB as Workbook
    Set mWB = ActiveWorkbook
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim strFileToOpen As String
    strFileToOpen = Application.GetOpenFilename _
        (Title:="Please choose a file to open", FileFilter:="Excel Files *.xls* (*.xls*),")
    Workbooks.Open Filename:=strFileToOpen
    Set targetedWB = Workbooks.Open(strFileToOpen)
    Dim LastRow As Long
    Call Copy_synopsis("B", "B2:C", "B2")   *** this procedure supposed to copy data to mWB
    Call Copy_synopsis("D", "D2:E", "D2")   *** but it is not copying the data
    Call Copy_synopsis("F", "F2:G", "F2")
    Call Copy_synopsis("H", "H2:I", "H2")
    Application.ScreenUpdating = True
    targetedWB.Close SaveChanges:=False
    On Error GoTo 0
  
End Sub

Sub Copy_synopsis(mcol As String, mrng As String, mcel As String)

    targetedWB.Activate   *** could this be the problem? or variable declaration?
    Sheets("synopsis").Select
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, mcol).End(xlUp).Row
    End With
    Range(mrng & LastRow).Select
    Selection.Copy
    mWB.Activate
    Range(mcel).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                :=False, Transpose:=False

End Sub

Mr. Hopkins

You do not only possess deep knowledge of excel but a very keen eye!

After all those to-and-fro, finally nailed it!!

Thank you very very much for your patience, time and assistance!!

more power to you..