Help with extracting uniform data from Multiple workbooks into one Master Data sheet in its own workbook

[Drazicc]

Hi Guys,

I am new to the forum and would appreciate some assistance with my problem as i have limited VBA coding knowledge.

I am currently trying to program a command button to extract data from the same column but from multiple workbooks that contain only one sheet, and to insert it into one master data set. For example, what im trying to acheive is this;.

Click button (I have named this Refresh data)within the master sheet
There are five columns that i have created in the master that i want to import data into from the corresponding seperate workbook, for example;
Extract data from workbook1 (Column B) and import that data into the Master Data sheet in (Column B)
Extract data from workbook2 (Column B) and import that data into the Master data sheet into (Column C)
Extract data from workbook3 (Column B) and import that data into the Master data sheet into (Column D)
Extract data from workbook4 (Column B) and import that data into the Master data sheet into (Column E)
Extract data from workbook5 (Column B) and import that data into the Master data sheet into (Column F)

All of these workbooks will be kept within the same file directory location so that on the click of a button, the macro will run and update the master data set.
Thank you in advance for your help

M

 

[Michael M]

Hi and welcome to the forum
I don't have Excel at the moment, so this is Untested
Note the comments in the code to change path and sheet names to suit

Public Sub test()
Dim wbk As Workbook, Filename As String, Path As String
Dim lr As Long, wb As Workbook, c As Integer
Application.ScreenUpdating = False
Set wb = ActiveWorkbook
lr2 = wb.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row 'change sheet name to suit
Path = "D:\Temp\TESTING\" ' CHANGE TO SUIT
Filename = Dir(Path & "*.xls")
'--------------------------------------------
'OPEN EXCEL FILES
c = 2
 Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)
            lr = Cells(Rows.Count, "B").End(xlUp).Row
            ws.Range("B2:B" & lr).Copy Destination:=wb.Sheets("Master").Cells(1, c) 'CHANGE sheet name TO SUIT
    wbk.Close True
    Filename = Dir
c = c + 1
Loop
MsgBox " FILES HAVE BEEN PROCESSED"
Application.ScreenUpdating = True
End Sub

 

[Drazicc]

Thank you for your response, it is much appreciated.
I am quite the amateur coder and cannot seem to get this to work, although your help is much appreciated

my workbooks are on the desktop in a folder called 'Macro Testing' , the master workbook is called 'TEST data MASTER.xlsm'

The workbooks are labelled 'Workbook1.xls' , 'Workbook2.xls' etc etc

I need to extract column B from each workbook, but the extracted data needs to be inserted into columns as mentioned above. Please respond to me as if i am a complete newbie

M

 

[Michael M]

Did you change the lines of code I mentioned.

 

[Drazicc]

Hi Michael - Thanks for your help, this worked for me. Much appreciated!!

 

[Michael M]

I don't know how it worked, I finally managed to get home and test it and it had errors !!!
This does work

Public Sub test()
Dim wbk As Workbook, Filename As String, Path As String
Dim lr As Long, wb As Workbook, c As Integer
Application.ScreenUpdating = False
Set wb = ThisWorkbook
lr2 = wb.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row 'change sheet name to suit
Path = "C:\Data\Testing\" ' CHANGE TO SUIT
Filename = Dir(Path & "*.xls")
'--------------------------------------------
'OPEN EXCEL FILES
c = 2
 Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
            lr = Cells(Rows.Count, "B").End(xlUp).Row
            Range("B2:B" & lr).Copy Destination:=wb.Sheets("Master").Cells(1, c) 'CHANGE sheet name TO SUIT
    wbk.Close True
    Filename = Dir
c = c + 1
Loop
MsgBox " FILES HAVE BEEN PROCESSED"
Application.ScreenUpdating = True
End Sub

 

[Drazicc]

Hey Michael - i used some of the concepts and decided to just record a macro that does it as it was much easier for me to create!!