Copy one worksheet to another from different workbook

[Jeffreyxx01]

Hi guys,

Can someone help me to write a code that copy one worksheet from one workbook to another worksheet in another workbook.
I need to automate a few tasks at work and I cannot make complex macro.

Thanks for your support.

 

[Jeffreyxx01]

Thanks Fluff, but still not working.

 

[Fluff]

Could you be a bit more explicit? "still not working" doesn't give me much to go on.

 

[Jeffreyxx01]

Ah sorry for that,

I received the error : Run-time error 424 Object Required ==> I press Debug and VBA highlight

.Range("A2:CF" & LastRow).Copy Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[Fluff]

You haven't declared, or set Sht

 

[Jeffreyxx01]

Sub OpenFiles()

   Dim InitPth As String
   Dim Wbk As Workbook
   Dim Cnt As Long
   
   InitPth = "W:\Insights Team\ALL EFL\UK\Reporting\RawData"
   
   With Application.FileDialog(3)
      .Title = "Select the files"
      .AllowMultiSelect = True
      .InitialFileName = InitPth
      If .Show <> -1 Then Exit Sub
   
      For Cnt = 1 To .SelectedItems.Count
         Set Wbk = Workbooks.Open(.SelectedItems(Cnt))
         
         With Wbk.Sheets("Report")
      LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A2:CF" & LastRow).Copy Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
      End With
            Application.DisplayAlerts = False
            Wbk.Close , False
      Next Cnt
   End With


End Sub

 

[Jeffreyxx01]

Hi Fluff,
I have declared Sht, but I dont really see how to set Sht now.

Sub OpenFiles()

   Dim InitPth As String
   Dim Wbk As Workbook
   Dim Cnt As Long
[COLOR=#ff0000]   Dim Sht As Worksheet[/COLOR]
   
   InitPth = "W:\Insights Team\ALL EFL\UK\Reporting\RawData"
   
   With Application.FileDialog(3)
      .Title = "Select the files"
      .AllowMultiSelect = True
      .InitialFileName = InitPth
      If .Show <> -1 Then Exit Sub
   
      For Cnt = 1 To .SelectedItems.Count
    Set Wbk = Workbooks.Open(.SelectedItems(Cnt))
        With Wbk.Sheets("Report")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("A2:CF" & LastRow).Copy Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        End With
            Application.DisplayAlerts = False
            Wbk.Close , False
      Next Cnt
   End With


End Sub

 

[Jeffreyxx01]

Hi Fluff,

I have set the Sht now however nothing is being copied from the sheets selected.
Do you have an idea why?

Sub OpenFiles()

   Dim InitPth As String
   Dim Wbk As Workbook
   Dim Cnt As Long
   Dim Sht As Worksheet
   
   InitPth = "W:\Insights Team\ALL EFL\UK\Reporting\RawData"
   
   With Application.FileDialog(3)
      .Title = "Select the files"
      .AllowMultiSelect = True
      .InitialFileName = InitPth
      If .Show <> -1 Then Exit Sub
   
      For Cnt = 1 To .SelectedItems.Count
    Set Wbk = Workbooks.Open(.SelectedItems(Cnt))
    Set Sht = ActiveWorkbook.Sheets("Report")
        With Wbk.Sheets("Report")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Sht.Range("A2:CF" & LastRow).Copy Sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        End With
            Application.DisplayAlerts = False
            Wbk.Close , False
      Next Cnt
   End With


End Sub

 

[Fluff]

You need to move this

Set Sht = ActiveWorkbook.Sheets("Report")

above where you open the new workbook.

When you open a workbook that book becomes the active book.
Alternatively if the sheet you are copying to is in the same book as the macro, change ActiveWorkbook to ThisWorkbook

 

[Jeffreyxx01]

I have tried to change, it does not actually work when I move above,
Also I want the macro to copy the data starting from the cell A3 in to the new workbook "clean"

Set Wbk = Workbooks.Open(.SelectedItems(Cnt))
Set Sht = ThisWorkbook.Sheets("Report")

Set Sht = ThisWorkbook.Sheets("Report")
Set Wbk = Workbooks.Open(.SelectedItems(Cnt))

 

[Fluff]

In post#87 you've added sht in front of the copy from sheet, so you are now copying from/to the same sheet