VBA: loop thru all worksheets, pull values

sdbeach

New Member
Joined
Aug 2, 2011
Messages
5
Greetings,

I am trying to make a macro that will be able to run on a workbook that contains multiple worksheets.

It will loop through all of the worksheets and copy values then insert them on a new sheet. The first value is always in J2. (it is a date)

The other values are located in columns F and B, starting at row 6.

The number of rows in these columns vary depending on the worksheet.

I will also use this on a number of different workbooks that contain a varying number of worksheets. So I can not just use the macro recorder to do this, since it will have variance in both the number of rows and the number of worksheets.

I will be happy to email a copy of some workbooks if anyone would like them.

Scott

Thank you in advance for any suggestions.
 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

FishingIsMoreFun

Board Regular
Joined
Sep 10, 2011
Messages
135
Try this code:
Code:
sub loopthroughworkbook()
Loop through all.worksheets
for first value = always.range("J2")
then do some stuff at colum f and b
start: row 6
if also other workbook.value then
do some.stuff
end sub
 

sdbeach

New Member
Joined
Aug 2, 2011
Messages
5
The problem I have is not knowing how to loop through a variable number of worksheets. I am new to VBA, usually a web guy... Thanks for helping out a newbie.

Also, when the macro loops, do you copy value then insert it on the new 'results' worksheet, or do you put the values in an array then print it on the new worksheet?

Thanks for the fast response!
 

sdbeach

New Member
Joined
Aug 2, 2011
Messages
5
I forgot a key point. The macro must only run on worksheets that are names using a numeric date. ie 7-4-2011.

Is there a IF_NUM method that can be used in VBA?
 

Watch MrExcel Video

Forum statistics

Threads
1,090,404
Messages
5,414,205
Members
403,519
Latest member
ckgyimah

This Week's Hot Topics

Top