Modify code to look through Specific Sheets instead of all the sheets.

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.

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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
just put do this

Code:
'shIndex = ActiveSheet.Index

' shIndex = shIndex + 1
note the makrs in front of the changes shIndex make it do more then one worksheet.
 
Upvote 0
Try something more like

Code:
Public Sub TSGen(IDKey As String)
        Sheets(2).Activate
        Range("A12").Activate
        shIndex = 2
 
        Do until shIndex = 4
 
            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
 
Upvote 0
Thanks for the quick response guys ill give those and shot and let you know how it works :D

Thanks again
 
Upvote 0
I added the following as suggested and i keep getting a Compile error stating Loop without Do

Code:
Public Sub TSGen(IDKey As String)
        Sheets(2).Activate
        Range("A12").Activate
        shIndex = 2
 
        Do Until shIndex = 4
 
            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

It will highlight the Public Sub TSGen(IDKey as String) in yellow and the Loop Until is highlighted in blue on the last line.

Do i need to add my code below that? i was inputting my code as follows:

Code:
Public Sub TSGen(IDKey As String)
        Sheets(2).Activate
        Range("A12").Activate
        shIndex = 2
 
        Do Until shIndex = 4
 
            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 CheckBox37 Then
                If IsEmpty(ActiveCell) = True And shIndex < Sheets.Count - 1 Then
                    shIndex = shIndex + 1
                    Sheets(shIndex).Activate
                    Range("A12").Activate
                End If
                Else
                    If IsEmpty(ActiveCell) = True And shIndex < Sheets.Count - 3 Then
                        shIndex = shIndex + 1
                        Sheets(shIndex).Activate
                        Range("A12").Activate
                    End If
            End If
        Loop Until IsEmpty(ActiveCell) = True
 
End Sub

Is that the wrong spot to put that checkbox code? I am still fairly new to VBA and may have messed something up adding that checkbox code.
 
Last edited:
Upvote 0
hmm, previous doesn´t make sense, the loop closes. But there´s 2 until´s

Do until ..

Loop


or

Do

Loop until

They must close, which, as it looks they do. But there´s multiple "until"

Try and remove "Until IsEmpty(ActiveCell) = True" from the last loop. Which i think is double.

Otherwise send me a copy of your sheet and i´ll have a look.
 
Last edited:
Upvote 0
OK thanks ill try that and see how it goes. If not ill send you a sample.


Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top