Losing Macro When Coping Sheets into a New Book

  • Thread starter Thread starter Vick
  • Start date Start date
V

Vick

I have a master spreadsheet that I'm using to create and send forms to
different people. Once they receive the form, I want them to complete it and
send it back to me.

However when I do this, I'm copying the form from a blank template in one
file into a new book. The button on the new file still refers back to the
macro on the orginal file, not the Macro on New file. (I placed the macro on
the sheet itself) Is there a way to get around this? Or maybe move a module
from one book to another through a macro? Any help would be greatly
appreciated
 
Hi Vick

Use a button from the control toolbox and add your code in the
click event of the button in the sheet module.
 
Thanks a bunch, that was so simple, I feel stupid for asking the question. I
did have another question however.

In the code on the control button I have it opening up a new file and trying
to select a cell in that file, but I keep getting a Application-defined or
object-defined error when it gets to the range selection. I've included the
code below.

Workbooks.Open Filename:="C:\Share\Tracking\Tracking Form.xls"
Range("A1").Select

Thanks again.
 
Try this

Dim wb2 As Workbook
Set wb2 = Workbooks.Open(Filename:="C:\Share\Tracking\Tracking Form.xls")
Application.Goto wb2.Worksheets(1).Range("A1")


I use the first worksheet now

Application.Goto wb2.Worksheets(1).Range("A1")

But you can also use a sheet name

Application.Goto wb2.Worksheets("YourSheetName").Range("A1")
 
Back
Top