Macro to copy data from source workbook sheets to historic workbook sheets

[Bobson]

My aim is to write a macro which will copy monthly data from the source, which is a workbook with data in multiple sheets, into my historic workbook which has the same multiple sheets but obviously the previous historic data as well.

So far I have managed to pull together some code which allow me to select the file, copy the range which will paste in the blank cell below the current data.

Sub copy()

    Dim wbSource As Workbook
    Dim wsDest As Worksheet
    Dim SourceFileName As String
    
    SourceFileName = Application.GetOpenFilename(FileFilter:="Excel Files (*xls),*xls", Title:="Please select a file")
    
    
    Set wsDest = ThisWorkbook.Worksheets("Sheet1")
    
    Application.ScreenUpdating = False
 
    Set wbSource = Workbooks.Open(Filename:=SourceFileName, ReadOnly:=True)
    
    wbSource.Sheets("Sheet1").Range("A1:C6").copy
    wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    wsDest.Range("A1").PasteSpecial xlPasteFormats
    
    wbSource.Close False
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
  
End Sub

What I need is to write code so this happens to all sheets at the same time. Sheet1 source workbook to Sheet1 in the historic workbook and so on... Sheet2 to Sheet2...... up to Sheet9 to Sheet9.

I am getting confused with how to Dimension the different sheets and would appreciate some help.

Do I need to dimension each sheet and repeat the code for every copy paste i want to do?

 

[Bobson]

So I have got this far but my loop just copies the final sheet into all the sheets.

Sub copy()

    Dim wbSource As Workbook, wsDest As Workbook
    Dim ws As Worksheet, rng As Range
    Dim SourceFileName As String
    
    Application.ScreenUpdating = False
        
    SourceFileName = Application.GetOpenFilename(FileFilter:="Excel Files (*xls),*xls", Title:="Please select a file")
    Set wsDest = ThisWorkbook
    
 
 
Set wbSource = Workbooks.Open(Filename:=SourceFileName, ReadOnly:=True)
    


For Each ws In wbSource.Sheets
Range("A1:C6").copy
        
        
wsDest.Sheets(ws.Name).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        
Next ws
    
    CutCopyMode = False
    wbSource.Close SaveChanges:=False
    Application.ScreenUpdating = True
 
End Sub

 

[Bobson]

Sub copy()

    Dim wbSource As Workbook, wsDest As Workbook
    Dim ws As Worksheet, rng As Range
    Dim SourceFileName As String
    Dim LastCol As Long
    




'I need to find a way of dealing with a variable column length..






    Application.ScreenUpdating = False
        
    SourceFileName = Application.GetOpenFilename(FileFilter:="Excel Files (*xls),*xls", Title:="Please select a file")
    Set wsDest = ThisWorkbook
  
Set wbSource = Workbooks.Open(Filename:=SourceFileName, ReadOnly:=True)
    


For Each ws In wbSource.Sheets




ws.Range(1, Cells(1, Columns.Count).End(xlToLeft)).copy
        
        
wsDest.Sheets(ws.Name).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        
Next ws
    
    CutCopyMode = False
    wbSource.Close SaveChanges:=False
    Application.ScreenUpdating = True
 
End Sub

I don't understand why this wont work