Hi,

The below macro basically copies a work sheet, opens a work book, pastes the copied data, closes the work sheet it just copied and then saves and closed the work book, then it reopens and repeats.

When it saves and closes and reopens the workbook it takes a long time.

Is there a way for it to only save and close the workbook once all the open work sheets to copied and pasted are completed?

Sub Mega_Dump()

Dim wbTarget As Workbook    'workbook where the data is to be pasted
Dim wbThis As Workbook      'workbook from where the data is to copied
Dim strName As String       'target workbook
Dim cn As String
Dim shName As String
Dim wasOPEN As Boolean

    
shName = Range("D6").Value
    
Set wbThis = ActiveWorkbook                                     'set to the current active workbook (the source book)
   
On Error Resume Next
Set wbTarget = Workbooks("My FILE PATH")           'try to find already open workbook

If Not wbTarget Is Nothing Then
    wasOPEN = True                                              'make a note that it was already open
Else                                                            'open it if it was closed, put your path in the space noted
    Set wbTarget = Workbooks.Open("My FILE PATH")
End If
      
wbTarget.Sheets(shName).Range("xfd1").EntireColumn.ClearContents  'select cell A1 on the target book
wbThis.Activate                                                 'activate the source book
Application.CutCopyMode = False                                 'clear any thing on clipboard to maximize available memory
  
wbThis.ActiveSheet.Range("b2:af44").Copy                         'copy the range from source book
                                                                'paste the data on the target book ( for just values PasteSpecial xlValues )
wbTarget.Sheets(shName).Cells(2, Columns.Count).End(xlToLeft).Offset(, 6).PasteSpecial xlPasteAll
 
Application.CutCopyMode = False                                 'clear any thing on clipboard to maximize available memory

If wasOPEN Then wbTarget.Save Else wbTarget.Close True          'save the target book, close it if it was closed before
 
wbThis.Close                                              'activate or close the source book again

Set wbTarget = Nothing                                          'clear memory
Set wbThis = Nothing

   Application.OnTime Now + TimeValue("00:00:01"), "Mega_Dump"
End Sub