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.

 

[Fluff]

Have you tried adapting the code in post#57?

 

[Jeffreyxx01]

I dont think it is going to work,
I have 4 workbooks, and I want to pull data from each separated workbook into 1,
All workbook have a title on A1 until S, so I want to pull data from A2 until S find last row, copy all of them at the empty followed line,
if you see what I meant?

 

[Jeffreyxx01]

Basically I am trying something like this but it is quite hard to make it

Sub Copy_data()   
   Dim Fname As String
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   Dim LastRow As Long


      
   Set Sht = ActiveWorkbook.Sheets("Clean")
   ChDrive "W:"
   ChDir "W:\Insights Team\ALL EFL\UK\Reporting\Bookings\FY17-18\Weekly"
   Fname = Application.GetOpenFilename(FileFilter:="xls Files (*.xls*), *.xls*", Title:="Select a file", MultiSelect:=False)
   If Fname = "False" Then
      MsgBox "no file selected"
      Exit Sub
   Else
   For i = 1 To 4
      Set Wbk = Workbooks.Open(Fname)
      With Wbk.Sheets("Report")
      LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A2:Y" & LastRow).Copy Sht.Range("A4")
      End With
      Set Wbk = Workbooks.Open(Fname)
    Wbk.Sheets ("Report1")
    Range("A2:Y" & LastRow).Copy Sht.Range("A" & Rows.Count).End(xlUp).Offset(1).Select
      End If
Next i


  Application.DisplayAlerts = False
      Wbk.Close , False
    Application.DisplayAlerts = False


End Sub

 

[Fluff]

One moment you're saying 4 sheets, now you're 4 workbooks. which is it?

 

[Jeffreyxx01]

I have 4 sheets in 4 different workbooks that is why I use the method of select the file as you shown.

 

[Fluff]

To open multiple workbooks you need to use something like this

Sub Open4Files()

   Dim InitPth As String
   Dim Wbk As Workbook
   Dim Cnt As Long
   
   InitPth = "W:\Insights Team\ALL EFL\UK\Reporting\Bookings\FY17-18\Weekly"
   
   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))
         
         ' Your code here
         
         Wbk.Close False
      Next Cnt
   End With

End Sub

 

[Jeffreyxx01]

Hi Fluff, the code in red does not work, it runs an error,
I want to copy every 4 report range A2:CF until the last row and copy to the cell A5 into the other file.
Also the 4 reports should be copied under each other on the other sheet by finding the empty row.
Does the code run that?

Thanks for your help.

Sub OpenFiles()

   Dim InitPth As String
   Dim Wbk As Workbook
   Dim Cnt As Long
   
   InitPth = "W:\xxxxxxxxxxxxxxxxxa"
   
   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
[COLOR=#ff0000]        .Range("A2:CF" & LastRow).Copy Sht.Range("A5" & Rows.Count).End(xlUp).Offset(1).Select[/COLOR]
      End With
            Application.DisplayAlerts = False
            Wbk.Close , False
      Next Cnt
   End With


End Sub

 

[Fluff]

This is the problem

[COLOR=#ff0000][/COLOR]Sht.Range([COLOR=#ff0000]"A5" & Rows.Count[/COLOR])[COLOR=#ff0000][/COLOR]

It is concatenatingA5 with Rows.Count, giving you A51048576 which is more rows than the sheet holds. Simply remove the 5 from A5

 

[Jeffreyxx01]

I have changed the code as you told me but it still does not work unfortunately.

 

[Fluff]

Remove the word select from the end