Macro to Move Worksheet to new book


N

Nimish

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
 
Ad

Advertisements

D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top