PDA

View Full Version : [SOLVED] Select specific sheets then run a copy row macro



Corbie
08-19-2014, 10:03 AM
Version: Excel 2013

What the Code Currently Does:
Copies any row in "Kits Racks" sheet, that has a "Y" in column J (starting on row 7), to the spreadsheet called "Full Equip List"
NOTE: THIS WORKS GREAT! :thumb Does almost exactly what I want it to do.

What I Want It to Do:
I want my code to select multiple (specific) sheets, then run the macro. The below code makes my macro run for ONE of those sheets. I need the line of code for selecting multiple specifically named sheets.
Clarification: I DO NOT want it to select all the sheets in the workbook. Just certain sheets, example: "Kits Racks", "PPE", and "Devices".

The Code Being Used:

Sub CopyRows()
Dim bottomL As Integer
bottomL = Sheets("Kits Racks").Range("J" & Rows.Count).End(xlUp).Row

Dim c As Range
For Each c In Sheets("Kits Racks").Range("j7:j" & bottomL)
If c.Value = "Y" Then
c.EntireRow.Copy Worksheets("Full Equip List").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next c

End Sub


(I don't need a spare empty line above each batch of results, but those are easily removed after running the macro. Every time I tried to remove it in the code itself, it would muck things up.)


Thank you for any help! Its been 2 days of tearing my hair out!

JKwan
08-19-2014, 10:56 AM
give this a try:

Sub CopyRows()
Dim WB As Workbook
Dim WS As Worksheet
Dim c As Range
Dim bottomL As Integer

Set WB = ActiveWorkbook

For Each WS In WB.Worksheets
Select Case WS.Name
Case Is = "Kits Racks", "PPE", "Devices"
bottomL = Sheets(WS.Name).Range("J" & Rows.Count).End(xlUp).Row

For Each c In Sheets(WS.Name).Range("j7:j" & bottomL)
If c.Value = "Y" Then
c.EntireRow.Copy Worksheets("Full Equip List").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next c
End Select
Next WS

Set WS = Nothing
Set WB = Nothing
End Sub

Corbie
08-19-2014, 11:20 AM
O. M. G!! You deserve internet hugs, cookies, and a beer!

Thats it exactly! Thanks a million!