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:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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,129,269
Messages
5,635,192
Members
416,846
Latest member
ImGoing2needaFormula

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
Top