Neltu
New Member
- Joined
- Jan 28, 2009
- Messages
- 25
Hello,
I have a Workbook that contains 7 Worksheets. Right now i have a macro that verifies which checkboxes the user clicks, and uses Sheet 1 as a key to located data based on an Identifier located on the first column of Sheets 2 - 6. Sheet 7 is the blank sheet where all data is pasted based on the identifiers the macros sees.
So when a user selects a checkbox, it searches the first sheet that matches what the Checkbox says, locates the first identifier, then scans all sheets that have the matching identifier and copies them to Sheet 7. It then steps down the column in the first sheet and continues the process until it runs into a blank cell at which point the macro ends.
Currently i am using the following code to step through and copy the identifiers outlined in the checkbox and pastes it on the Sheet 7 or Last sheet.
Is there anyway possible to have it only search sheets 2 - 4?
I need to add additional checkbox at my bosses request to exclude sheets 5 and 6 unless a specific checkbox is added. I have written that macro already however i just need to stop the code above from searching Sheets 5 and 6.
Is it possible to modify the code above to just look through sheets 2 - 4?
Thanks in advance (if this isnt completly ignored like my last question :'()
I have a Workbook that contains 7 Worksheets. Right now i have a macro that verifies which checkboxes the user clicks, and uses Sheet 1 as a key to located data based on an Identifier located on the first column of Sheets 2 - 6. Sheet 7 is the blank sheet where all data is pasted based on the identifiers the macros sees.
So when a user selects a checkbox, it searches the first sheet that matches what the Checkbox says, locates the first identifier, then scans all sheets that have the matching identifier and copies them to Sheet 7. It then steps down the column in the first sheet and continues the process until it runs into a blank cell at which point the macro ends.
Currently i am using the following code to step through and copy the identifiers outlined in the checkbox and pastes it on the Sheet 7 or Last sheet.
Code:
Public Sub TSGen(IDKey As String)
Sheets(2).Activate
Range("A12").Activate
shIndex = ActiveSheet.Index
Do
If ActiveCell.Value = IDKey Then
ActiveCell.EntireRow.Copy
Sheets(Sheets.Count).Activate
Range("A12").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.PasteSpecial
End If
Sheets(shIndex).Activate
ActiveCell.Offset(1, 0).Select
If IsEmpty(ActiveCell) = True And shIndex < Sheets.Count - 1 Then
shIndex = shIndex + 1
Sheets(shIndex).Activate
Range("A12").Activate
End If
Loop Until IsEmpty(ActiveCell) = True
End Sub
Is there anyway possible to have it only search sheets 2 - 4?
I need to add additional checkbox at my bosses request to exclude sheets 5 and 6 unless a specific checkbox is added. I have written that macro already however i just need to stop the code above from searching Sheets 5 and 6.
Is it possible to modify the code above to just look through sheets 2 - 4?
Thanks in advance (if this isnt completly ignored like my last question :'()
Last edited: