VBA to copy databases from multiple workbooks into current workbook

[Chookz]

Hey guys,
I have 4 different databases in 4 different workbooks.
I need to create a macro to open each of the 4 workbooks, copy the databases and paste them into 4 separate sheets in my current workbook.
Is anyone able to help me with a VBA code?

Use these assumptions and I'll fill in the correct details.

The 4 workbooks are located on the desktop:
C:\Users\callen\Desktop\Workbook 1
C:\Users\callen\Desktop\Workbook 2
C:\Users\callen\Desktop\Workbook 3
C:\Users\callen\Desktop\Workbook 4

The databases are located in a sheet called RawData in each of the 4 workbooks

This range will select the data in the database to be copied: Range("OFFSET(A1, 0, 0, COUNTA(A1:A50000), 431)").Select

The sheets in my current workbook for the databases to be pasted are called:
DB1
DB2
DB3
DB4
With DB1 corresponding to the database from Workbook 1 etc. The data can be pasted in cell A1

The current workbook is also located on the desktop and is called DatabaseMaster

I think that should be all the details, thanks in advance to anyone who is able to help. This will save me a huge amount of time
Cheers,

 

[JLGWhiz]

This should work.

Sub copystuff()
Dim wb As Workbook, sWb As Workbook, sh As Worksheet, sPath As String
Dim lr As Long, i As Long
Set wb = ThisWorkbook
sPath = "C:\Users\callen\Desktop\"
     For i = 1 To 4
        Set sWb = Workbooks.Open("Workbook" & i & ".xlsx")
        Set sh = sWb.Sheets("RawData")
        lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
        sh.Range("A1", sh.Cells(lr, 431)).Copy wb.Sheets("DB" & i).Range("A1")
    Next
End Sub

 

[Chookz]

Thanks for the quick reply JLGWhiz. One problem is that my databases aren't actually all on the desktop and aren't called Workbook 1, 2 etc, and the sheet names aren't DB 1, 2 etc. Example Workbook 1 is actually called Training Database, Workbook 2 is called Rehab Database.
I just put those down to make it easier as opposed to giving heaps of details. Are you able to give me a code along the lines of:

Sum copystuff()
'Database 1
Enter code and ill replace the path, wb name and sheet name with the correct ones

'Database 2
Code

'Database 3
Code

'Database 4
Code

End Sub

Or something similar that I can edit in the correct details?

 

[Fazza]

Another way is to set up a query table to pull the data from the workbooks, and have them refresh on file open. No VBA or formulas required. The source files are not opened

To set up one query, ALT-D-D-N and follow the wizard. Along the way you can filter or aggregate or do lots of other things. If you get a message about no visible tables, choose options and then system tables: this will show the worksheet names. Google and Excel help have further info.

HTH

 

[Chookz]

Another way is to set up a query table to pull the data from the workbooks, and have them refresh on file open. No VBA or formulas required. The source files are not opened

To set up one query, ALT-D-D-N and follow the wizard. Along the way you can filter or aggregate or do lots of other things. If you get a message about no visible tables, choose options and then system tables: this will show the worksheet names. Google and Excel help have further info.

HTH

Hmm I never even knew about query tables. I'll have a look. But I need this done preferably by tomorrow morning, so if anyone has any idea for a vba code it would be much appreciated