PDA

View Full Version : Cycling through sheets with loop



gteamer
12-07-2016, 02:03 PM
Hello,

I am trying to cycle through several worksheets, and I am using an if to skip the first sheet ("Recap").

I want to open a datasheet for each sheet in the workbook, and copy paste data, then close the datasheet.

Everything is working exactly as I wanted it to, but the loop doesn't go to the next worksheet. It just keeps looping the first one over and over.

What am I missing here?


Dim WS As Worksheet
For Each WS In ThisWorkbook.Sheets
If WS.Name <> "Recap" Then
Dim scorecardName As String
scorecardName = Range("B1").Value
MsgBox ActiveWorkbook.ActiveSheet.Name

Workbooks.Open "\\ROGIMAGE12\Sales\Analytics\WM\Reports\RL Queries\" & scorecardName & ".xls?"
Cells.Find(What:="Vendor Summary : Jacmel", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(-2, 0).EntireRow, ActiveCell.End(xlDown)).Copy
Workbooks("Walmart Report Card Recap Template").Activate
ActiveSheet.Cells.Find(What:="Vendor Summary : Jacmel", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).PasteSpecial xlPasteAll
Workbooks(scorecardName).Close savechanges:=False

End If
Next WS

Aussiebear
12-07-2016, 02:30 PM
[QUOTE=gteamer;353417]
What am I missing here?/QUOTE]

Code Tags! Please wrap your code within
.... your code tags.

gteamer
12-07-2016, 02:31 PM
Thank you! I am sorry, very new to all of this.

JKwan
12-07-2016, 02:49 PM
Are you sure?
Are you referring to the message box keep saying "Recap"? If so, this is because you are using "ActiveWorkbook.ActiveSheet.Name", you should use "WS.Name". Another way is


WS.Select
msgbox ActiveWorkbook.ActiveSheet.Name

gteamer
12-08-2016, 07:08 AM
If my sheet names are Recap, Sheet1, Sheet2, and Sheet3. The macro is giving me a message that says the value of cell B1 in Sheet 1, I click Okay, then it opens the file with the name the same as B1. Copies and pastes the data. Then it just repeats that for Sheet1 over and over, but I want it to go to Sheet2 and then Sheet3 until there are no more sheets.

I hope that is clear. I apologize if it isn't clear.

JKwan
12-08-2016, 08:12 AM
try changing this

scorecardName = Range("B1").Value

to

scorecardName = WS.Range("B1").Value

This is because you are referencing "active" sheet, unless you switch your sheet (message 4)

gteamer
12-08-2016, 09:30 AM
Thank you JKwan!!

Now the correct file is opening, and it is cycling through the files correctly, but there are two problems.

1. If I am on the "Recap" sheet, it tries to open a book called Recap. Shouldn't the IF skip the Recap sheet, or am I misunderstanding that.

2. If I start on another sheet, Sheet1 for example, then it opens all of the right books in order, but it pastes in Sheet1. I would like it to open Sheet1, then paste in Sheet1. Then open Sheet2 and paste in Sheet2 and so on.

Here is my updated code.


Sub Scorecards()
'
' Scorecards Macro

Dim WS As Worksheet
For Each WS In ThisWorkbook.Sheets
If WS.Name <> "Recap" Then
Dim scorecardName As String
scorecardName = WS.Range("B1").Value
MsgBox scorecardName

Workbooks.Open "\\Scorecards\" & scorecardName & ".xls?"
Cells.Find(What:="Vendor Summary : Jacmel", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(-2, 0).EntireRow, ActiveCell.End(xlDown)).Copy
Workbooks("Walmart Report Card Recap Template").ActiveSheet.Activate
ActiveSheet.Cells.Find(What:="Vendor Summary : Jacmel", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).PasteSpecial xlPasteAll
Application.DisplayAlerts = False
Workbooks(scorecardName).Close savechanges:=False
Application.DisplayAlerts = True

End If
Next WS

End Sub



Is it this line, or something I forgot to add?

Thank you for any help!

JKwan
12-08-2016, 09:54 AM
hmm, there must be something really minute. It works for me. Recap sheet never got executed for me by using your code. Now, I would double check your sheet name, it will filter ONLY if it is "Recap". Just to be explicit, "recap". "Recap " - note the space and case of the sheet name will make a difference, the code will execute.

gteamer
12-08-2016, 12:08 PM
I made one small change, and now it works...I changed the line in question on my previous post from


Workbooks("Walmart Report Card Recap Template").ActiveSheet.Activate

to


Workbooks("Walmart Report Card Recap Template").Sheets(scorecardName).Activate

Thank you for all of your help!!!


Sub Scorecards()
'
' Scorecards Macro

Dim WS As Worksheet
For Each WS In ThisWorkbook.Sheets
If WS.Name <> "Recap" Then

Dim scorecardName As String
scorecardName = WS.Range("B1").Value
MsgBox scorecardName

Workbooks.Open "\\ROGIMAGE12\Sales\Analytics\WM\Reports\RL Queries\Scorecards\" & scorecardName & ".xls?"
Cells.Find(What:="JACMEL", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(-2, 0).EntireRow, ActiveCell.End(xlDown)).Copy
Workbooks("Walmart Report Card Recap Template").Sheets(scorecardName).Activate
ActiveSheet.Cells.Find(What:="JACMEL", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).PasteSpecial xlPasteAll
Application.DisplayAlerts = False
Workbooks(scorecardName).Close savechanges:=False
Application.DisplayAlerts = True

End If
Next WS

End Sub