Consolidating the data from multiple workbooks to one master workbook

[sat1591]

Hi All,

I have created a macro for Consolidating the data from multiple workbooks to one master workbook.

I am actually beginner for VBA. But I have referred many books and gone through websites for learning and successfully coded the below.

The below VBA code which I have created is by my own but I have picked few codes by inspiring which posted in websites or forums. That can be considered as inspiration.

The below VBA code is working brilliant and highly satisfied for my work. But my query is my code is looking very big. Can we make a compact sized macro? I wanted to know in what way i can simply this code.

Sub Consolidate()Dim Count_Row As Double
Dim Count_Col As Double
Dim Range_Col As Range
Dim Range_Row As Range
Dim Range_Col2 As Range
Dim Range_Row2 As Range
Consolidate_File = ActiveWorkbook.Name
Action_1:
Dest_File = Application.GetOpenFilename()
Do While Dest_File <> False
Workbooks.Open Dest_File
My_File = ActiveWorkbook.Name
Set Range_Col = ActiveSheet.Columns(1)
Set Range_Row = ActiveSheet.Rows(1)
Count_Row = Application.WorksheetFunction.CountA(Range_Col)
Count_Col = Application.WorksheetFunction.CountA(Range_Row)
'Header Copy
If Workbooks(Consolidate_File).Sheets(1).Range("A1") = 0 Then
Range("A1:" & Cells(1, Count_Col).Address(rowabsolute:=False, columnabsolute:=False)).Copy
Workbooks(Consolidate_File).ActiveSheet.Paste
Application.CutCopyMode = False
End If
Workbooks(My_File).Activate
Range("A2:" & Cells(Count_Row, Count_Col).Address(rowabsolute:=False, columnabsolute:=False)).Select
Selection.Copy
Workbooks(Consolidate_File).Activate
Set Range_Col2 = ActiveSheet.Columns(1)
Set Range_Row2 = ActiveSheet.Rows(1)
Count_Row2 = Application.WorksheetFunction.CountA(Range_Col2)
Count_Col2 = Application.WorksheetFunction.CountA(Range_Row2)
Range(Cells(Count_Row2 + 1, 1).Address(rowabsolute:=False, columnabsolute:=False)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(My_File).Close
GoTo Action_1
Loop
Workbooks(Consolidate_File).ActiveSheet.Range("A1").CurrentRegion.Select
Selection.Columns.AutoFit
End Sub

Thanks in advance.

 

[dsandoz116]

I am a novice for VBA, so won't be able to help you consolidate it, but had a question for you. The code you wrote is doing exactly what I want, but when I run it with my worksheets, it copies only the first column of the second workbook I chose. Do you have any fixes I might try? It only copies to Column K and I need it to go all the way to Column AP and down 1000 rows.

 

[sat1591]

I am also new to VBA but I can give a try to solve your question.

1) Do your worksheet first column has all the Columns name filled?
2) For "it Copies only first column of second workbook" But I believe that would be impossible. Could you check it once again?