Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

move between worksheets

  Asked By: Durril    Date: Oct 31    Category: MS Office    Views: 908
  

I'm trying to pick up data from a workbook via VBA, the workbook has
13 sheets (1 per month + a summary).

I want to itterate over all worksheets, but my code keeps just going
over the first actvive one... not sure where I'm going wrong.

the code I'm using to change is


y = 1
<start loop>
wb.worksheets(y).activate

<do stuff>

y = y + 1
<end loop>


I can post the full code if needed, but I was just wondering what the
correct syntex for moving over all worksheets was?

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Debbie Reyes     Answered On: Oct 31

For Each ws in Sheets
ws.Activate
<do stuff>
Next ws

 
Answer #2    Answered By: Leroy Schmidt     Answered On: Oct 31

It's all working ok now though. Looks like I was being a noob and
making a silly error (though it wasn't an obvious one to me).
Thought I'd share since it's possible a future newbie might encounter
the same problem.

anyway...

What I was doing (wrong) was (not indicated in first post):

-------------------------------------
sub_x
<snip>

y = 1
Do While y <= NumberOfSheets


wb.Worksheets(y).Activate
lastrow = wb.Sheets(y).Cells.SpecialCells(xlCellTypeLastCell).row

row = 3
Do While row <= LastRow
acctNo = Cells(row, 2).Value
CoName = Cells(row, 1).Value
acctMan = Cells(row, 3).Value
revenue = Cells(row, 16).Value


<snip>
Loop
y = y + 1
<snip>
End Sub
-----------------------------------------------------------

Now for each sheet I was recording the name, so I know that the y = x
iteration was working correctly because I had a record of the name of
each worksheet... but the data  was the same in each case...

where I was going wrong  was in not fully declaring the location of
each cell (I was assuming that "cells(r,c)" would refer to that cell
in the active worksheet... but it didn't.

When I changed the above part of the code  (shown below) then it
worked fine (ie adding a "wb.Worksheets(y)." before each "cells()"
reference.

-------------------------------------------------

sub_x
<snip>

y = 1
Do While y <= NumberOfSheets


wb.Worksheets(y).Activate
lastrow = wb.Sheets(y).Cells.SpecialCells(xlCellTypeLastCell).row

row = 3
Do While row <= LastRow
acctNo = wb.Worksheets(y).Cells(row, 2).Value
CoName = wb.Worksheets(y).Cells(row, 1).Value
acctMan = wb.Worksheets(y).Cells(row, 3).Value
revenue = wb.Worksheets(y).Cells(row, 16).Value


<snip>
Loop
y = y + 1
<snip>
End Sub
--------------------------------------------------------------

 
Didn't find what you were looking for? Find more on move between worksheets Or get search suggestion and latest updates.




Tagged: