KIM Chanthan
Member
Dear everyone,
I am very basic in VBA. I have just created one Code in order to consolidate data from different workbook into one single workbook. However, I feel my code is too long and I think VBA expert would have the better way to make it shorts and simple. Can you help to guide me some about this trouble?
Below is the original code which I have just created.
Thanks for your strong support.
MOD EDIT: ADDED CODE TAGS.
I am very basic in VBA. I have just created one Code in order to consolidate data from different workbook into one single workbook. However, I feel my code is too long and I think VBA expert would have the better way to make it shorts and simple. Can you help to guide me some about this trouble?
Below is the original code which I have just created.
Thanks for your strong support.
Code:
Sub Consolidate()
'Try with workbook name "A"
Workbooks.Open Filename:="C:\Users\USER\Desktop\Test\VBA\New folder\A.xlsm"
Sheets("Province1").Select
Range("B1:B3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("A1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
Windows("A.xlsm").Activate
Range("D1:D3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("D1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
Windows("A.xlsm").Activate
Sheets("Province2").Select
Range("B1:B3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("A1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
Windows("A.xlsm").Activate
Range("D1:D3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("D1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
'Try another workbook name "B"
Workbooks.Open Filename:="C:\Users\USER\Desktop\Test\VBA\New folder\B.xlsm"
Sheets("Province3").Select
Range("B1:B3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("A1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
Windows("B.xlsm").Activate
Range("D1:D3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("D1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
Windows("B.xlsm").Activate
Sheets("Province4").Select
Range("B1:B3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("A1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
Windows("A.xlsm").Activate
Range("D1:D3").Select
Selection.Copy
Windows("Consolidate.xlsm").Activate
Range("D1048576").Select
ActiveCell.Cells.End(xlUp).Select
ActiveCell.Cells.Offset(1, 0).Select
ActiveCell.PasteSpecial , Transpose:=True
ActiveWorkbook.Save
Application.Quit
End Sub
MOD EDIT: ADDED CODE TAGS.
Last edited by a moderator: