I have a macro that searches through a designated folder and checks for a
specific inputbox value and copies each sheet that matches that critieria.

How ever each sheet that is copied has numerous buttons with macros asigned
to them, that are set to operate when they are in the workbook they were
created in.
However, as one of these buttons are designed to DELETE the current sheet, i
would like to be able to have macro to also work when it is copied into the
file that searches and copies the sheets into it.

If i simply copy the code from one of these files into the search files
code, i get an error as the macros are looking to the file they come from
rather than to the code in the file the sheet was copied to.
Is there a way around this, to copy a sheet into the search file and to have
the macro work from that new file?

--
Regards

Corey

If you're copying the worksheet -- not just copy|pasting the cells, you could
use a commandbutton from the Control Toolbox toolbar. The code behind those
type of commandbuttons will travel with the worksheet--since the code is in the
worksheet module.

If you used a button from the Forms toolbar and you already have code in the new
workbook ready to be assigned to that button on the copied worksheet, you could
assign the macro to the button at the same time you copy the sheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim wkbk As Workbook
Dim TestButton As Button

Set wkbk = Workbooks("book1.xls")

For Each wks In wkbk.Worksheets
If LCase(wks.Range("a1").Value) = "asdf" Then
wks.Copy _
befo=ThisWorkbook.Worksheets(1)

With ActiveSheet
Set TestButton = Nothing
On Error Resume Next
Set TestButton = .Buttons(1)
On Error GoTo 0

If TestButton Is Nothing Then
'button wasn't found
Else
TestButton.OnAction _
= "'" & ThisWorkbook.Name & "'!" & "MyMacro"
End If
End With
End If
Next wks

End Sub

Yep. Each button will have to be assigned to its macro kind of like this.

You can assign the macro to the button(s) manually, too.

Corey wrote:

I have a macro that searches through a designated folder and checks for a
specific inputbox value and copies each sheet that matches that critieria.

How ever each sheet that is copied has numerous buttons with macros asigned
to them, that are set to operate when they are in the workbook they were
created in.
However, as one of these buttons are designed to DELETE the current sheet, i
would like to be able to have macro to also work when it is copied into the
file that searches and copies the sheets into it.

If i simply copy the code from one of these files into the search files
code, i get an error as the macros are looking to the file they come from
rather than to the code in the file the sheet was copied to.
Is there a way around this, to copy a sheet into the search file and to have
the macro work from that new file?

--
Regards

Corey

--

Dave Peterson

if the controls are from the forms toolbar, change the onaction property to
refer to the correct macro.

If from the control toolbox toolbar, the code should be copied with the
sheet. Sounds like the first case.

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
I have a macro that searches through a designated folder and checks for a
specific inputbox value and copies each sheet that matches that critieria.

How ever each sheet that is copied has numerous buttons with macros
asigned to them, that are set to operate when they are in the workbook
they were created in.
However, as one of these buttons are designed to DELETE the current sheet,
i would like to be able to have macro to also work when it is copied into
the file that searches and copies the sheets into it.

If i simply copy the code from one of these files into the search files
code, i get an error as the macros are looking to the file they come from
rather than to the code in the file the sheet was copied to.
Is there a way around this, to copy a sheet into the search file and to
have the macro work from that new file?

--
Regards

Corey

Thanks for the reply
Yes i use the buttons from the forms tool bar.
But i am a little baffled as to how to do what you are saying.
The code to copy the searches worksheet is below:

Sub ExampleTest()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter a CUSTOMER Name (Use from
Examples)", "Company Name for Title..")
input2 = Application.InputBox("Enter The Customer's CONVEYOR Name (Use
from Examples)", "Company Name for Title..")
SaveDriveDir = CurDir
MyPath = "\\Office2\my documents\Costing Sheets"
' ChDrive MyPath
' ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("B3").Value = input1 And
mybook.Worksheets(i).Range("D3").Value = input2 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

' ChDrive SaveDriveDir
' ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop
End Sub

The above code is located in the original workbook , in a MODULE.

I coped this and placed a copy in a new module, in the search file, but it
can up with an error after running for a bit.

--
Regards

Corey


"Dave Peterson" wrote in message
...
If you're copying the worksheet -- not just copy|pasting the cells, you
could
use a commandbutton from the Control Toolbox toolbar. The code behind
those
type of commandbuttons will travel with the worksheet--since the code is
in the
worksheet module.

If you used a button from the Forms toolbar and you already have code in
the new
workbook ready to be assigned to that button on the copied worksheet, you
could
assign the macro to the button at the same time you copy the sheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim wkbk As Workbook
Dim TestButton As Button

Set wkbk = Workbooks("book1.xls")

For Each wks In wkbk.Worksheets
If LCase(wks.Range("a1").Value) = "asdf" Then
wks.Copy _
befo=ThisWorkbook.Worksheets(1)

With ActiveSheet
Set TestButton = Nothing
On Error Resume Next
Set TestButton = .Buttons(1)
On Error GoTo 0

If TestButton Is Nothing Then
'button wasn't found
Else
TestButton.OnAction _
= "'" & ThisWorkbook.Name & "'!" & "MyMacro"
End If
End With
End If
Next wks

End Sub

Yep. Each button will have to be assigned to its macro kind of like this.

You can assign the macro to the button(s) manually, too.

Corey wrote:

I have a macro that searches through a designated folder and checks for a
specific inputbox value and copies each sheet that matches that
critieria.

How ever each sheet that is copied has numerous buttons with macros
asigned
to them, that are set to operate when they are in the workbook they were
created in.
However, as one of these buttons are designed to DELETE the current
sheet, i
would like to be able to have macro to also work when it is copied into
the
file that searches and copies the sheets into it.

If i simply copy the code from one of these files into the search files
code, i get an error as the macros are looking to the file they come from
rather than to the code in the file the sheet was copied to.
Is there a way around this, to copy a sheet into the search file and to
have
the macro work from that new file?

--
Regards

Corey

--

Dave Peterson

It's not the code that does the copying that's a problem. It's that the button
remains assigned to the macro in the original workbook.

You have a few choices.

Use commandbuttons from the Control toolbox toolbar--so that the code travels
with the worksheet.

Remove the buttons completely and add only the buttons that you want to support
to the newly copied worksheets--assign each of the buttons to an existing macro
in that workbook.

Reassign the buttons to an existing macro within the consolidated workbook.


Corey wrote:

Thanks for the reply
Yes i use the buttons from the forms tool bar.
But i am a little baffled as to how to do what you are saying.
The code to copy the searches worksheet is below:

Sub ExampleTest()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter a CUSTOMER Name (Use from
Examples)", "Company Name for Title..")
input2 = Application.InputBox("Enter The Customer's CONVEYOR Name (Use
from Examples)", "Company Name for Title..")
SaveDriveDir = CurDir
MyPath = "\\Office2\my documents\Costing Sheets"
' ChDrive MyPath
' ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("B3").Value = input1 And
mybook.Worksheets(i).Range("D3").Value = input2 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

' ChDrive SaveDriveDir
' ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop
End Sub

The above code is located in the original workbook , in a MODULE.

I coped this and placed a copy in a new module, in the search file, but it
can up with an error after running for a bit.

--
Regards

Corey

"Dave Peterson" wrote in message
...
If you're copying the worksheet -- not just copy|pasting the cells, you
could
use a commandbutton from the Control Toolbox toolbar. The code behind
those
type of commandbuttons will travel with the worksheet--since the code is
in the
worksheet module.

If you used a button from the Forms toolbar and you already have code in
the new
workbook ready to be assigned to that button on the copied worksheet, you
could
assign the macro to the button at the same time you copy the sheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim wkbk As Workbook
Dim TestButton As Button

Set wkbk = Workbooks("book1.xls")

For Each wks In wkbk.Worksheets
If LCase(wks.Range("a1").Value) = "asdf" Then
wks.Copy _
befo=ThisWorkbook.Worksheets(1)

With ActiveSheet
Set TestButton = Nothing
On Error Resume Next
Set TestButton = .Buttons(1)
On Error GoTo 0

If TestButton Is Nothing Then
'button wasn't found
Else
TestButton.OnAction _
= "'" & ThisWorkbook.Name & "'!" & "MyMacro"
End If
End With
End If
Next wks

End Sub

Yep. Each button will have to be assigned to its macro kind of like this.

You can assign the macro to the button(s) manually, too.

Corey wrote:

I have a macro that searches through a designated folder and checks for a
specific inputbox value and copies each sheet that matches that
critieria.

How ever each sheet that is copied has numerous buttons with macros
asigned
to them, that are set to operate when they are in the workbook they were
created in.
However, as one of these buttons are designed to DELETE the current
sheet, i
would like to be able to have macro to also work when it is copied into
the
file that searches and copies the sheets into it.

If i simply copy the code from one of these files into the search files
code, i get an error as the macros are looking to the file they come from
rather than to the code in the file the sheet was copied to.
Is there a way around this, to copy a sheet into the search file and to
have
the macro work from that new file?

--
Regards

Corey

--

Dave Peterson

--

Dave Peterson

Is there a trick to asigning a MACRO to a Command button?
It does not seem to be a right click and asign macro?

--
Regards

Corey
"Dave Peterson" wrote in message
...
It's not the code that does the copying that's a problem. It's that the
button
remains assigned to the macro in the original workbook.

You have a few choices.

Use commandbuttons from the Control toolbox toolbar--so that the code
travels
with the worksheet.

Remove the buttons completely and add only the buttons that you want to
support
to the newly copied worksheets--assign each of the buttons to an existing
macro
in that workbook.

Reassign the buttons to an existing macro within the consolidated
workbook.


Corey wrote:

Thanks for the reply
Yes i use the buttons from the forms tool bar.
But i am a little baffled as to how to do what you are saying.
The code to copy the searches worksheet is below:

Sub ExampleTest()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter a CUSTOMER Name (Use from
Examples)", "Company Name for Title..")
input2 = Application.InputBox("Enter The Customer's CONVEYOR Name
(Use
from Examples)", "Company Name for Title..")
SaveDriveDir = CurDir
MyPath = "\\Office2\my documents\Costing Sheets"
' ChDrive MyPath
' ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("B3").Value = input1 And
mybook.Worksheets(i).Range("D3").Value = input2 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

' ChDrive SaveDriveDir
' ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop
End Sub

The above code is located in the original workbook , in a MODULE.

I coped this and placed a copy in a new module, in the search file, but
it
can up with an error after running for a bit.

--
Regards

Corey

"Dave Peterson" wrote in message
...
If you're copying the worksheet -- not just copy|pasting the cells, you
could
use a commandbutton from the Control Toolbox toolbar. The code behind
those
type of commandbuttons will travel with the worksheet--since the code
is
in the
worksheet module.

If you used a button from the Forms toolbar and you already have code
in
the new
workbook ready to be assigned to that button on the copied worksheet,
you
could
assign the macro to the button at the same time you copy the sheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim wkbk As Workbook
Dim TestButton As Button

Set wkbk = Workbooks("book1.xls")

For Each wks In wkbk.Worksheets
If LCase(wks.Range("a1").Value) = "asdf" Then
wks.Copy _
befo=ThisWorkbook.Worksheets(1)

With ActiveSheet
Set TestButton = Nothing
On Error Resume Next
Set TestButton = .Buttons(1)
On Error GoTo 0

If TestButton Is Nothing Then
'button wasn't found
Else
TestButton.OnAction _
= "'" & ThisWorkbook.Name & "'!" & "MyMacro"
End If
End With
End If
Next wks

End Sub

Yep. Each button will have to be assigned to its macro kind of like
this.

You can assign the macro to the button(s) manually, too.

Corey wrote:

I have a macro that searches through a designated folder and checks
for a
specific inputbox value and copies each sheet that matches that
critieria.

How ever each sheet that is copied has numerous buttons with macros
asigned
to them, that are set to operate when they are in the workbook they
were
created in.
However, as one of these buttons are designed to DELETE the current
sheet, i
would like to be able to have macro to also work when it is copied
into
the
file that searches and copies the sheets into it.

If i simply copy the code from one of these files into the search
files
code, i get an error as the macros are looking to the file they come
from
rather than to the code in the file the sheet was copied to.
Is there a way around this, to copy a sheet into the search file and
to
have
the macro work from that new file?

--
Regards

Corey

--

Dave Peterson

--

Dave Peterson

While in design mode (another icon on that control toolbox toolbar), just double
click on the commandbutton.

You'll be whisked off to the location the code resides--under that worksheet in
a procedure like:

Private Sub CommandButton1_Click()

Corey wrote:

Is there a trick to asigning a MACRO to a Command button?
It does not seem to be a right click and asign macro?

--
Regards

Corey
"Dave Peterson" wrote in message
...
It's not the code that does the copying that's a problem. It's that the
button
remains assigned to the macro in the original workbook.

You have a few choices.

Use commandbuttons from the Control toolbox toolbar--so that the code
travels
with the worksheet.

Remove the buttons completely and add only the buttons that you want to
support
to the newly copied worksheets--assign each of the buttons to an existing
macro
in that workbook.

Reassign the buttons to an existing macro within the consolidated
workbook.


Corey wrote:

Thanks for the reply
Yes i use the buttons from the forms tool bar.
But i am a little baffled as to how to do what you are saying.
The code to copy the searches worksheet is below:

Sub ExampleTest()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter a CUSTOMER Name (Use from
Examples)", "Company Name for Title..")
input2 = Application.InputBox("Enter The Customer's CONVEYOR Name
(Use
from Examples)", "Company Name for Title..")
SaveDriveDir = CurDir
MyPath = "\\Office2\my documents\Costing Sheets"
' ChDrive MyPath
' ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("B3").Value = input1 And
mybook.Worksheets(i).Range("D3").Value = input2 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

' ChDrive SaveDriveDir
' ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop
End Sub

The above code is located in the original workbook , in a MODULE.

I coped this and placed a copy in a new module, in the search file, but
it
can up with an error after running for a bit.

--
Regards

Corey

"Dave Peterson" wrote in message
...
If you're copying the worksheet -- not just copy|pasting the cells, you
could
use a commandbutton from the Control Toolbox toolbar. The code behind
those
type of commandbuttons will travel with the worksheet--since the code
is
in the
worksheet module.

If you used a button from the Forms toolbar and you already have code
in
the new
workbook ready to be assigned to that button on the copied worksheet,
you
could
assign the macro to the button at the same time you copy the sheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim wkbk As Workbook
Dim TestButton As Button

Set wkbk = Workbooks("book1.xls")

For Each wks In wkbk.Worksheets
If LCase(wks.Range("a1").Value) = "asdf" Then
wks.Copy _
befo=ThisWorkbook.Worksheets(1)

With ActiveSheet
Set TestButton = Nothing
On Error Resume Next
Set TestButton = .Buttons(1)
On Error GoTo 0

If TestButton Is Nothing Then
'button wasn't found
Else
TestButton.OnAction _
= "'" & ThisWorkbook.Name & "'!" & "MyMacro"
End If
End With
End If
Next wks

End Sub

Yep. Each button will have to be assigned to its macro kind of like
this.

You can assign the macro to the button(s) manually, too.

Corey wrote:

I have a macro that searches through a designated folder and checks
for a
specific inputbox value and copies each sheet that matches that
critieria.

How ever each sheet that is copied has numerous buttons with macros
asigned
to them, that are set to operate when they are in the workbook they
were
created in.
However, as one of these buttons are designed to DELETE the current
sheet, i
would like to be able to have macro to also work when it is copied
into
the
file that searches and copies the sheets into it.

If i simply copy the code from one of these files into the search
files
code, i get an error as the macros are looking to the file they come
from
rather than to the code in the file the sheet was copied to.
Is there a way around this, to copy a sheet into the search file and
to
have
the macro work from that new file?

--
Regards

Corey

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson