Dear Experts
I have tried to put together various bits of code from examples to do as described below but being a newbie, I have not been successful. I am getting an error that says object not support this property or method but I know that there are others. I am way over my head but I am learning and this is the exciting part.
Here is what I would like to do:
1. Using an identified path containing that have the required workbook files, open the workbooks, then go through each worksheet and copy the specified range of cells ( this range is the same for each worksheet - C3-C10).
These copied ranges would then be pasted into adjacent columns of a specific worksheet ( GraphData) of a master workbook (MasterGen).
I do have some additional conditions that I do not know how to do:
1. When using MacOs (mac), how do you specify the path for the "Const sPath"?
2. How do I specify that the "Definition" worksheet to be excluded from the selection, for both the workbooks sources and the master? I have tried to code this.
3. How can I title each copied column in the master with the source worksheet name?
Option Explicit
Sub CombineMultipleFiles()
Const sPath = "c:\"
Dim sFile As String
Dim wbkSource As Workbook
Dim wSource As Worksheet
Dim wTarget As Worksheet
Dim lColumns As Long
Dim lMaxSourceColumn As Long
Dim lMaxTargetColumn As Long
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Set wTarget = ActiveWorkbook.Worksheets("GraphData")
lColumns = wTarget.Columns.Count
sFile = Dir(sPath & "*.xls*")
Do While Not sFile = ""
Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
For Each wSource In wbkSource.Worksheets
If wSource.Name <> wSource("Definition") Then
lMaxSourceColumn = wSource.Cells(lColumns, 1).End(xlUp).Column
lMaxTargetColumn = wTarget.Cells(1, lColumns).End(xlToLeft).Column
wSource.Range("C3:C10").Copy Destination:=wTarget.Cells(lMaxTargetColumn + 1, 3)
wbkSource.Close SaveChanges:=False
sFile = Dir
End If
Next
Loop
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub