Gronk_h8er
Board Regular
- Joined
- Jan 6, 2009
- Messages
- 63
Hi All,
You helped me a while ago putting together a macro in the thread, "Combining Multiple Cells in Multiple Worksheets in Multiple Workbooks into one Table", which, as the thread name indicates, collected specific cells from multiple workbooks and put them into one workbook for me.
I know have a similar issue that expands upon this idea, yet seems to me be slightly more difficult.
Whereas originally, the cells being pulled each time where the same in each workbook, i now need to create a macro that will look into a workbook and pull the names of multiple sheets for use as headings, and pull cells from an array that will be different in size for each workbook.
This is quite tricky to explain in words and i wish i could show you, but i don't know how.
Maybe first things first, how can i change the original macro, listed below, to look at the names of the worksheets and pull them as headings for my report?
Please tell me anything you need that will make this easier for you! I will do my best to accomodate.
I really appreciate any help you can give me!
Sub test()
Dim myDir As String, fn As String, temp As String, ref As String
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
myDir = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
n = 2
fn = Dir(myDir & "*.xls*")
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
n = n + 1: temp = "='" & myDir & "[" & fn & "]"
With ThisWorkbook.Sheets("sheet1") '<- change to actual sheet name
ref = temp & "Cover Sheet'!"
.Cells(n, "a").Formula = ref & "D7"
ref = temp & "Value-Ease'!"
.Cells(n, "b").Resize(, 3).Formula = _
Array(ref & "F10", ref & "F23", ref & "L26")
.Cells(n, "k").Resize(, 4).Formula = _
Array(ref & "F10", ref & "F14", ref & "F16", ref & "F10")
ref = temp & "Strategic Supply Positioning'!"
.Cells(n, "e").Resize(, 6).Formula = _
Array(ref & "F14", ref & "F12", ref & "F32", ref & "F34", _
ref & "R35", ref & "F30")
.Cells(n, "o").Resize(, 4).Formula = _
Array(ref & "F20", ref & "F18", ref & "F26", ref & "F28")
ref = temp & "Potential Options'!"
.Cells(n, "t").Resize(, 40).Formula = _
Array(ref & "I14", ref & "I15", ref & "I16", ref & "I17", ref & "I18", ref & "I19", ref & "I20", ref & "I21", ref & "I22", ref & "I23", ref & "I24", ref & "I25", ref & "I26", ref & "I27", ref & "I28", ref & "I29", ref & "I30", ref & "I31", ref & "F43", ref & "F44", ref & "F45", ref & "F46", ref & "F47", ref & "F48", ref & "F49", ref & "F50", ref & "F51", ref & "F53", ref & "F54", ref & "F55", ref & "F57", ref & "F58", ref & "F60", ref & "F61", ref & "F63", ref & "F64", ref & "F65", ref & "F66", ref & "F67", ref & "F68")
.Rows(1).Value = .Rows(1).Value
End With
End If
fn = Dir
Loop
End Sub
You helped me a while ago putting together a macro in the thread, "Combining Multiple Cells in Multiple Worksheets in Multiple Workbooks into one Table", which, as the thread name indicates, collected specific cells from multiple workbooks and put them into one workbook for me.
I know have a similar issue that expands upon this idea, yet seems to me be slightly more difficult.
Whereas originally, the cells being pulled each time where the same in each workbook, i now need to create a macro that will look into a workbook and pull the names of multiple sheets for use as headings, and pull cells from an array that will be different in size for each workbook.
This is quite tricky to explain in words and i wish i could show you, but i don't know how.
Maybe first things first, how can i change the original macro, listed below, to look at the names of the worksheets and pull them as headings for my report?
Please tell me anything you need that will make this easier for you! I will do my best to accomodate.
I really appreciate any help you can give me!
Sub test()
Dim myDir As String, fn As String, temp As String, ref As String
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
myDir = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
n = 2
fn = Dir(myDir & "*.xls*")
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
n = n + 1: temp = "='" & myDir & "[" & fn & "]"
With ThisWorkbook.Sheets("sheet1") '<- change to actual sheet name
ref = temp & "Cover Sheet'!"
.Cells(n, "a").Formula = ref & "D7"
ref = temp & "Value-Ease'!"
.Cells(n, "b").Resize(, 3).Formula = _
Array(ref & "F10", ref & "F23", ref & "L26")
.Cells(n, "k").Resize(, 4).Formula = _
Array(ref & "F10", ref & "F14", ref & "F16", ref & "F10")
ref = temp & "Strategic Supply Positioning'!"
.Cells(n, "e").Resize(, 6).Formula = _
Array(ref & "F14", ref & "F12", ref & "F32", ref & "F34", _
ref & "R35", ref & "F30")
.Cells(n, "o").Resize(, 4).Formula = _
Array(ref & "F20", ref & "F18", ref & "F26", ref & "F28")
ref = temp & "Potential Options'!"
.Cells(n, "t").Resize(, 40).Formula = _
Array(ref & "I14", ref & "I15", ref & "I16", ref & "I17", ref & "I18", ref & "I19", ref & "I20", ref & "I21", ref & "I22", ref & "I23", ref & "I24", ref & "I25", ref & "I26", ref & "I27", ref & "I28", ref & "I29", ref & "I30", ref & "I31", ref & "F43", ref & "F44", ref & "F45", ref & "F46", ref & "F47", ref & "F48", ref & "F49", ref & "F50", ref & "F51", ref & "F53", ref & "F54", ref & "F55", ref & "F57", ref & "F58", ref & "F60", ref & "F61", ref & "F63", ref & "F64", ref & "F65", ref & "F66", ref & "F67", ref & "F68")
.Rows(1).Value = .Rows(1).Value
End With
End If
fn = Dir
Loop
End Sub