Macro to Move Worksheet to new book
 

Why does this line of code :
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
produce an error saying object out of range?

I'm trying to set parameters to call a file and load it into a
workbook. From there, I will run another macro to clean the file.

Any help?


Sub Auto_Open()
' This macro will put today's date as the default new tab name
Sheets("Menu").Select
Range("D5").Select
Selection.Formula = "=text(now(),""mmm dd yyyy"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Selection.Columns.AutoFit
Range("D8").Value = ""
End Sub

Sub GetFile()
Sheets("Menu").Select
PathName = Range("D3").Value
Filename = Range("D4").Value
TabName = Range("D5").Value

ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
Sheets("Menu").Select
Range("D8").Select
ActiveCell.Value = "Completed"
Range("D9").Select
End Sub

Macro to Move Worksheet to new book
 

Since controlfile holds the name of the activeworkbook, it's not the after:=
portion causing the error.

That means that it has to be the sheets(tabname) that's the problem.

So the value in D5 of the Menu sheet isn't the name of a sheet.

Nimish wrote:

Why does this line of code :
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
produce an error saying object out of range?

I'm trying to set parameters to call a file and load it into a
workbook. From there, I will run another macro to clean the file.

Any help?

Sub Auto_Open()
' This macro will put today's date as the default new tab name
Sheets("Menu").Select
Range("D5").Select
Selection.Formula = "=text(now(),""mmm dd yyyy"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Selection.Columns.AutoFit
Range("D8").Value = ""
End Sub

Sub GetFile()
Sheets("Menu").Select
PathName = Range("D3").Value
Filename = Range("D4").Value
TabName = Range("D5").Value

ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
Sheets("Menu").Select
Range("D8").Select
ActiveCell.Value = "Completed"
Range("D9").Select
End Sub

--

Dave Peterson