largeselection
Active Member
- Joined
- Aug 4, 2008
- Messages
- 358
I'm close to having the macro done. My question is how can I have a macro run on a different sheet than the one I am inputting selections in?
Basically I have a first sheet which has checkboxes on it and when I select them the corresponding columns from a data sheet copy and then paste onto a results sheet. The problem I'm having is that currently when I select the checkboxes (I have them in a userform now, but they will be moved to a sheet instead) it does the macro as I select them. So I select a checkbox and it goes to the data sheet-copies, then the results sheet-pastes, and then back to the data sheet to copy the next column selected, etc.
This slows down the run time and seems unnecessary. How can I alter this so that I can select the checkboxes on the first sheet and have it do all the copy/pastes between the second/third tabs while remaining on the first sheet.
I want to be able to select the checkboxes I want, then have it signal that it is done, and then go to the results sheet and have it be there.
Please help me experts!
Here's the code I'm working with:
Sub Builder(MyHeader As String, cbTrueFalse As Boolean)
Dim MyColumn As Integer
Const LastColumn As Integer = 250 '<<< change to Maximum number of columns
For MyColumn = 1 To LastColumn
If Cells(1, MyColumn) = MyHeader Then
If cbTrueFalse = True Then
Columns(MyColumn).Select
Selection.Copy
Sheets("Results").Select
On Error Resume Next
Range("A1").End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Else
End If
Exit Sub
End If
Next MyColumn
End Sub
Basically I have a first sheet which has checkboxes on it and when I select them the corresponding columns from a data sheet copy and then paste onto a results sheet. The problem I'm having is that currently when I select the checkboxes (I have them in a userform now, but they will be moved to a sheet instead) it does the macro as I select them. So I select a checkbox and it goes to the data sheet-copies, then the results sheet-pastes, and then back to the data sheet to copy the next column selected, etc.
This slows down the run time and seems unnecessary. How can I alter this so that I can select the checkboxes on the first sheet and have it do all the copy/pastes between the second/third tabs while remaining on the first sheet.
I want to be able to select the checkboxes I want, then have it signal that it is done, and then go to the results sheet and have it be there.
Please help me experts!
Here's the code I'm working with:
Sub Builder(MyHeader As String, cbTrueFalse As Boolean)
Dim MyColumn As Integer
Const LastColumn As Integer = 250 '<<< change to Maximum number of columns
For MyColumn = 1 To LastColumn
If Cells(1, MyColumn) = MyHeader Then
If cbTrueFalse = True Then
Columns(MyColumn).Select
Selection.Copy
Sheets("Results").Select
On Error Resume Next
Range("A1").End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Else
End If
Exit Sub
End If
Next MyColumn
End Sub