I’m new to Macros and VBA and I was hoping that someone could help me with a task I want to complete, here are the details;
I have a folder and in that folder are a number of Excel workbooks. For this task I need to be able to consolidate the data from a range of cells in a particular worksheet (called Appendix B in each one) in these workbooks into one worksheet called Master (in a separate workbook). This is what I have so far in terms of code, and it works great.
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "C:\Users\cmarsh\Desktop\group_1"
strExtension = Dir("*.xls*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
LastRow = .Sheets("Appendix B").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Appendix B").Range("A1:D" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
strExtension = Dir
Application.ScreenUpdating = True
However I want to expand on this so that instead of the data going down the page it goes horizontally across the page, I also want to add the source workbook name to the top of each section, please see an example below;
Required result example
Is this possible???
Any help would be appreciated.