-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Write Code to Open Other WOrkbooks
- Thread starter Justinian
- Start date
S O WXLS
New Member
- Joined
- Oct 11, 2014
- Messages
- 38
Run this in a sub from your "main" workbook:
Code:
For i = 1 To 8
Set OtherWB = Workbooks.Open("C:\Folder\SubFolder\" & i & ".xls") '// Change path as required
For Each WS in OtherWB.Sheets
WS.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
Next WS
OtherWB.Close False
Set OtherWB = Nothing
Next i
Upvote
0
Well, the sheets I import from the other workbooks have the same name but I rename name after I add them. So I add the sheet, then rename it 1, 2, all the way through 8. How do I incorporate the renaming of the sheets? Here is my code:
Sub Macro1()
'
'
Workbooks.Open Filename:= _
"C:\Users\justin\Desktop\1.xls"
Sheets("Cyber Rain Controller Events").Select
Sheets("Cyber Rain Controller Events").Move Before:=Workbooks( _
"Cyber-Rain Macro.xlsm").Sheets(1)
Sheets("Cyber Rain Controller Events").Select
Sheets("Cyber Rain Controller Events").Name = "1"
Workbooks("1.xls").Close SaveChanges:=False
End Sub
I know this will insert a sheet and rename it so i guess my question is how to loop it?
Sub Macro1()
'
'
Workbooks.Open Filename:= _
"C:\Users\justin\Desktop\1.xls"
Sheets("Cyber Rain Controller Events").Select
Sheets("Cyber Rain Controller Events").Move Before:=Workbooks( _
"Cyber-Rain Macro.xlsm").Sheets(1)
Sheets("Cyber Rain Controller Events").Select
Sheets("Cyber Rain Controller Events").Name = "1"
Workbooks("1.xls").Close SaveChanges:=False
End Sub
I know this will insert a sheet and rename it so i guess my question is how to loop it?
Upvote
0
S O WXLS
New Member
- Joined
- Oct 11, 2014
- Messages
- 38
Just out of curiosity - try this:
If that doesn't work, then try this:
Code:
For i = 1 To 8
With CreateObject("C:\Users\justin\Desktop\" & i & ".xls").Sheets("Cyber Rain Controller Event")
.Name = i
.Copy Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End With
Next iIf that doesn't work, then try this:
Code:
For i = 1 To 8
Set OtherWB = Workbooks.Open("C:\Users\justin\Desktop\" & i & ".xls")
With OtherWB.Sheets("Cyber Rain Controller Events")
.Name = i
.Copy Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End With
OtherWB.Close False
Set OtherWB = Nothing
Next i
Upvote
0
Similar threads
- Solved
