Problem coping worksheet to workbook, Macro does attach

[judgeh59]

I have written some code that copies a worksheet, which creates a new workbook, which I save to a file. One of the thing on the old worksheet/new workbook, is a macro button. When I open the new file the macro button is there and the macro is there, because I looked in the list of macros, but when I click on the macro button on the worksheet it seems to be "tied" to the old file. The code for the macro is written in the worksheet portion so it does transfer when I copy. I tried writing the code in a standard module but it didn't copy when I copied the worksheet and saved. Here is the save the code and then the macro code. Thanks

this code is in the standard module area of the worksheet.

Sub SaveWorksheet()

Dim DirName As String
Dim MyFileName As String
Dim ParsePos As Integer
Dim PathName As String

Randomize
DirName = "Network file area"

Sheets(FeederListSheet).Activate
ParsePos = ParseString(Cells(2, 2).Value)
MyFileName = Replace(RTrim(Mid(Cells(2, 2), 1, ParsePos - 2)), ".", "_") _
& "_" & Int(Rnd * 1000000)
PathName = DirName & MyFileName

Sheets(FeederVerificationSheet).Copy
ActiveWorkbook.SaveAs FileName:=PathName
ActiveWorkbook.Close

End Sub

This code is in the worksheet area...

Public Sub SecondTable()

Dim MaxRows As Integer
Dim LastRow As Integer
Dim i As Integer
Dim StartRow As Integer

MaxRows = 200
StartRow = 3

LastRow = Cells(MaxRows, 1).End(xlUp).Row

If LastRow < 71 Then
For i = StartRow To LastRow
Cells(i, 1).Value = Cells(i, 1).Value + 70
Next i
Else
MsgBox "You have more than 70 Feeders in this list", vbInformation
End If

End Sub

then, of course, I have text box on the worksheet that is assigned the macro SecondTable. The macro fires normally in the original worksheet, only after I do the copy and open the new worksheet does the macro button get confused. And so do I...thanks for the help...





Be Alert, America more lerts