switch between open workbooks within macro

in this code a target file is is opened and saved 3 times which is unnnecessary but I dont know how to change it. After the first instance of "paste" in workbook opened by the macro, rather than close it I would just like to instruct the macro to activate the previous worksheet before continuing.

This system was OK with a small test file but unusable where it is needed

Any help appreciated

I tried with this method at line 21 Windows("c:\users\nigel.MACROOMHAULAGE\My Documents\vehicle costing.xlsm").Activate but no success

Sub Macro2()

If Application.WorksheetFunction.CountA(Range("c4:c330")) = 0 Then
    MsgBox "No Data highlighted!"
    Exit Sub
End If


MsgBox "Make Sure Maintenance File is closed before clicking OK - "

Dim rng As Range
Dim rfiltered As Range
Dim path, ws_name As String
ws_name = ActiveSheet.Name
ScreenUpdating = False


    With ActiveWorksheet
    Set rng = Range("a3:t" & Range("a65500").End(xlUp).Row)

    rng.AutoFilter Field:=3, Criteria1:="M"
'MsgBox "1"
      path = Mid(ActiveWorkbook.FullName, 1, Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.Name))
      
    
    Range("a4:b" & Range("c65500").End(xlUp).Row).Copy
 

    
        Workbooks.Open ("c:\users\nigel.MACROOMHAULAGE\My Documents\maintenance.xlsm")
    
            Sheets(ws_name).Activate
            NextRow = Range("B14").End(xlUp).Row + 1
            Range("B" & NextRow).Select
 
    
    
            ActiveSheet.Paste
            Windows("c:\users\nigel.MACROOMHAULAGE\My Documents\vehicle costing.xlsm").Activate
            'ActiveWorkbook.Save
            'ActiveWorkbook.Close
    MsgBox "2"
                Range("e4:e" & Range("c65500").End(xlUp).Row).Copy
    
                Workbooks.Open ("c:\users\nigel.MACROOMHAULAGE\My Documents\maintenance.xlsm")
    
                Sheets(ws_name).Activate
                NextRow = Range("d14").End(xlUp).Row + 1
                Range("d" & NextRow).Select
                
    
                ActiveSheet.Paste
    
                ActiveWorkbook.Save
                ActiveWorkbook.Close
    
    
            Range("g4:j" & Range("c65500").End(xlUp).Row).Copy
    
                Workbooks.Open ("c:\users\nigel.MACROOMHAULAGE\My Documents\maintenance.xlsm")
    
                Sheets(ws_name).Activate
                NextRow = Range("f14:i14").End(xlUp).Row + 1
                Range("f" & NextRow).Select
                
    
                ActiveSheet.Paste
    
    Dim Ans As Long
    
    Ans = MsgBox("Save Update?", vbYesNo)
    
    If Ans = vbYes Then
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    ElseIf Ans = vbNo Then
    ActiveWorkbook.Close
    End If
    
    

    
   rng.AutoFilter
   
   Range("c3:c300").ClearContents

    

    End With


End Sub

I have tried to use the ThisWorkbook.Activate command to refer to the source file (this is where the code is)

and I tried to set the target file by using

Sub Macro3()

Dim WB2 As Workbook
Set WB2 = ("c:\users\nigel.MACROOMHAULAGE\My Documents\maintenance.xlsm")

but i get an error at this line

I thought that once the code activated the worksheet in the "target file" it would stay there and I could call back to the source file using the Thisworkbook.activate command

In reality the code completes but after the first paste and return to source file no other data is transferred and the wrong file (the source file) closes. So the first instance of the target file is not remaing active.

Any ideas because code would be ideal to use in this format

Try this, it should provide some ideas to help you.

I've taken your code and modified it.
I'm still learning myself so i'm sure someone else could refine it much more than me.

I haven't tested it but this method has worked for me on other projects.

Option Explicit

Sub test()

    Dim wbInitial As Workbook           ' Variable to hold workbook active when macro starts
    Dim wsInitial As Worksheet          ' Variable to hold worksheet active when macro starts
    Dim wbWorking As Workbook           ' Variable to hold target workbook
    Dim wsWorking As Worksheet          ' Variable to hold target worksheet
    Dim strWorking As String            ' Variable to hold target worksheet name
    Dim NextRow As Long                 ' Variable to hold row number

    ' Store Active Workbook to variable
    Set wbInitial = ActiveWorkbook
    
    ' Store Active Worksheet to variable
    Set wsInitial = wbInitial.ActiveSheet
    
    'store Active sheet name
    ws_name = ActiveSheet.Name

    wbInitial.Activate      ' set initial workbook active
    wsInitial.Activate      ' Set initial worksheet active
    
    ' Select range to data from
    wsInitial.Range("a4:b" & Range("c65500").End(xlUp).Row).Copy
    
    ' Store target workbook name
    strWorking = "p:\My Documents\truck files 2008\maintenance issues.xlsm"
    
    ' Open target File
    Set wbWorking = Workbooks.Open(strWorking)
    
    ' store target worksheet with name ws_name to variable wsWorking
    Set wsWorking = wbWorking.Sheets(ws_name)
    
    ' Set the target worksheet active
    wsWorking.Activate
    
    ' get next empty row in column "B"
    NextRow = Range("B14").End(xlUp).Row + 1
    
    ' Make the next empty cell in column "B" active
    Range("B" & NextRow).Select

    ' Paste range in from other worksheet
    wsWorking.Paste
    
    ' First copy operation complete, go back for next
    ' ***********************************************
    
    wbInitial.Activate      ' set initial workbook active
    wsInitial.Activate      ' Set initial worksheet active
    
    ' Select range to data from
    Range("e4:e" & Range("c65500").End(xlUp).Row).Copy

    wbWorking.Activate      ' set target workbook active
    wsWorking.Activate      ' Set target worksheet active
    
    ' Paste range in from other worksheet
    wsWorking.Paste
    
    ' Second copy operation complete
    ' ******************************
    
    ' Close Target workbook
    ' ---------------------
    
    Dim Ans As Long
    
    Ans = MsgBox("Save Update?", vbYesNo)
    
    If Ans = vbYes Then
        wbWorking.Close (True)
    ElseIf Ans = vbNo Then
        wbWorking.Close (False)
    End If
    

End Sub

Although I just tested the code below on two seperate sheets and was able to set values of cells in the active sheet from cells on a sheet in another workbook.

activesheet.range("E8").value = workbooks(2).Worksheets(1).range("G8").value

You could substitute in

wbInitial.Worksheets(ws_name).range("G8").value

and use a For Next loop to interate through the range and copy the values from the selected range to the destination.

Forgot to add that the "With" part of the code in your first post is not necessary. The "With" statement allows you to access the methods and properties of the object included in the "With" statement by starting the code line with just a period, saving having to tye the objects name over and over again.

The "With" statement allows you to put this sort of code together.

    With wbWorking
        
        .Activate
        wsWorking = .ActiveSheet
        ws_name = .Name
        Path = .Path
        .Close
        
    End With

Which is the same as

        wbWorking.Activate
        wsWorking = wbWorking.ActiveSheet
        ws_name = wbWorking.Name
        Path = wbWorking.Path
        wbWorking.Close