VBA Loop through Worksheets - Rookie needs help.

[nintendods]

Hi Guys,

I searched for similar questions but have not found any, and searching google i have done the same and used some code but cant figure it out. I am not super literate when it comes to excel...

Essentially my question is this. I have a tipping competition and have built a tip sheet that is sent out and returned. It is designed using combo box / cell link and an index function to produce a single row containing the name of the tipper and their weekly tips. I have then tweaked a macro to import this calculation sheet from each returned file in a directory.

So now i have a master workbook, with about 100 'calculation' sheets, numbered (1), (2), (3) etc.

I am looking to cut a range, B2:B22, paste into the master sheet, then insert a row so as to move it down and make room for the next cut and paste. Then i need it to loop through each worksheet.

Range("B2:B22").select
Selection.Copy
Sheets("Master Sheet").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("9:9").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown


does anyone know a simple way to do it? I have used code before but all that happens is it cuts and pastes on the master sheet 100 times...

thanks heaps, i have been looking online all day but cannot find anything to work properly. Getting frustrated and though someone could help

thanks!

 

[RivetHed]

Hi,

If you want to loop through your worksheets you need to declare a worksheet variable.

Dim w as worksheet

for each w in activeworkbook
'Do stuff
next w

Obviously you don't want to keep pasting over the data in your master sheet either so declare a range variable on that sheet to hold your copied data in and resize it using currentregion whenever you paste.

 

[xlbo]

this is why you keep pasting over the same data:

Range("A9").Select
Selection.PasteSpecial

does not change - you continually paste to A9

There are LOTS of examples of very similar code here at Tek Tips

The code that Rivethed has provided will loop through all your worksheets

I would not recommend that you insert a row and paste above the prior pasted data - you are better off pasting below. for that you will need a range incrementer

Dim w as worksheet
Dim i as long
i=2
for each w in activeworkbook

If w.name M <> "MasterSheet" then
w.range("B2:B22").copy destination = sheets("MasterSheet").cells(i,1)
i = i + 20
end if
next w

where MasterSheet is the name of your aggregation sheet

Are you really saying you couldn;t find anything like this in the archives?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question