How do I open sheets and consolidate them into 1 file with multiple sheets?
I have a user to input 20 store numbers into cells A1 to A20. Cells B1 to B20 will have the hyperlink that each store number has, for example: cell B1 to B20 will have the following formula: =HYPERLINK("C:\"Store Number "&A1&".xls")
- How do I open all these hyperlinks at once without having to click cells B1 to B20 (20 times)?
- What if the user only wants to see 10 files instead of 20? If I create a VBA that opens all hyperlinks in cells B1 to B20 but the user only input 10 stores, I will have a debug problem.
- After these files are open, how do I consoli开发者_如何学Pythondate them into 1 file with up to 20 sheets/tabs? Each store file has the same format and has the same sheet name: "Sheet1".
Thanks so much for the help!!
This might get you started for part 3.
Sub ConsolidateAllSheet1()
Dim wbCons As Excel.Workbook
Dim wb As Excel.Workbook
Dim wbName As String
Set wbCons = Workbooks.Add()
For Each wb In Application.Workbooks
wbName = wb.Name
If wbName Like "Store Number*" And _
wbName <> ThisWorkbook.Name Then
wb.Sheets("Sheet1").Copy _
after:=wbCons.Sheets(wbCons.Sheets.Count)
wbCons.Sheets(wbCons.Sheets.Count).Name = _
Replace(wbName, ".xls", "")
'wb.Close False 'save without changes
End If
Next wb
End Sub
and welcome to StackOverflow. I found a good answer to your questions 1 & 2 here. (But as the OP commented, these are for web hyperlinks, not file hyperlinks.)
I have not solved Q 3 with VBA, but I have done this manually, by right-clicking on the tab to be copied and then choosing "Move or copy" in the context pop-up. I then copy the sheet to the newly-created book.
EDIT: I would suggest that you change the Hyperlinks to just plain text. Then the following VBA will open the file names in the selected cells.
Dim fileName As String
For Each vCell In Selection
fileName = vCell.Value
Workbooks.Open fileName
Next vCell
精彩评论