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]

So I have tried that, taking only one file, it does copy the file into the one I want, however, I only have 1 files copied, when I select 4 files, it does not copy.
Do you see 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 = ThisWorkbook.Sheets("Datadump")
        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]

I understand the problem, I I was selecting all the books instead of one by one, now it seems to work.
If I want to use the slide down formula for my formula, I just have to declare and set?

 

[Fluff]

If you have a look back at post#23 that should show you how to do it.

 

[Jeffreyxx01]

The macro works well now after adding the other things. Thank you.

 

[Fluff]

Your welcome

 

[Jeffreyxx01]

Can I ask your help on another macro?
I am quite stuck at finding why the lastrow function does not work.

 

[Jeffreyxx01]

Hi Fluff,
I tried to adapt this code to another of my file.
I have a problem, I want to paste 4 files from different workbook, this work fine in the macro below,
However now I need to have a find row and copy the files after each other. I tried to change the code slightly but it did not work, it was copying on top of my first copied file.
Do you know a way to arrange that?

:D

Sub OpenFiles()

   Dim InitPth As String
   Dim Wbk As Workbook
   Dim Cnt As Long
   Dim Sht As Worksheet
   Dim Usdrws 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))
    Set Sht = ThisWorkbook.Sheets("Data")
        With Wbk.Sheets("Report")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("A2:CF" & LastRow).Copy Sht.Range([COLOR=#ff0000]"A11")[/COLOR].End(xlUp).Offset(1)
        End With
            Application.DisplayAlerts = False
            Wbk.Close , False
      Next Cnt
   End With
End Sub

 

[Fluff]

Simply create a new variable & assign a value to it like you have done with LastRow