VBA to copy data from multiple workbooks into new master workbook

[MSpicewood]

Good morning,


I'm a VBA novice and having a hard time with my first major project. I've been researching solutions to copy all the data from multiple files (.XLS?) in a directory called "C:/allfiles/" to a newly created master report file.

I need a solution that won't capture any additional blank rows as each file has a different number of entries. I'm also trying to only pull from the first worksheet on each workbook file. These files have a second tab called LOG which I'm trying to exclude.

I have referenced a previous topic here to get started but it's not working out as my specifications are a bit different. Referenced: https://www.mrexcel.com/forum/excel...ata-multiple-workbooks-into-master-sheet.html

Any help is greatly appreciated. Thank you.

 

[Fluff]

Do you want all the data to go onto 1 sheet?

 

[mumps]

Do you want to copy each sheet to one sheet in the master report or each sheet to a separate sheet? You want to delete all the blank rows before copying the sheets. Is this correct?

 

[Fluff]

If you want all sheets to go to 1 master sheet, try

Sub CopyFiles()
   Dim Mws As Worksheet
   Dim Wbk As Workbook
   Dim Fname As String
   Const Pth As String = "C:\AllFiles\"
   
   Set Mws = ThisWorkbook.Sheets("[COLOR=#ff0000]Master[/COLOR]")
Application.ScreenUpdating = False
   Fname = Dir("*.xls*")
   Do While Fname <> ""
      Set Wbk = Workbooks.Open(Pth & Fname)
      Wbk.Sheets(1).Range("A1").CurrentRegion.Copy Mws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      Wbk.Close False
      Fname = Dir
   Loop
End Sub

Change the value in red to suit

 

[MSpicewood]

Do you want to copy each sheet to one sheet in the master report or each sheet to a separate sheet? You want to delete all the blank rows before copying the sheets. Is this correct?

I'd like to copy the first sheet in each file only into the master report. As long as the ranges target the entered data and no further than deleting the blank rows won't be necessary. I hope that makes sense.

 

[mumps]

It looks like Fluff has provided a solution for you.

 

[MSpicewood]

I'm getting an error. Debug is highlighting the following as an issue:
Set Wbk = Workbooks.Open(Pth & Fname)

The first worksheet in my master report file is named "Master". The directory is definitely "C:\AllFiles".

 

[Fluff]

Change this line as shown

   Fname = Dir([COLOR=#ff0000]Pth &[/COLOR] "*.xls*")

 

[MSpicewood]

Change this line as shown

   Fname = Dir([COLOR=#ff0000]Pth &[/COLOR] "*.xls*")

I'm still getting the same error unfortunately.

 

[Fluff]

If you add this message box

   Do While Fname <> ""
      MsgBox Pth & vbLf & Fname
      Set Wbk = Workbooks.Open(Pth & Fname)

What does it say & does it open the file?
Also what error are you getting?