Looping macro

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Hi Guys,

My first question. I need help with the following.

I have a workbook named Consol.xls that currently includes three worksheets named DIV001, DIV002 and DIV003 respectively. The prefix "DIV" will be constant for additional worksheets but the remainder of the name may not necessarily be sequential. The workbook contains other worksheets, the names of which are not prefixed with "DIV"

Totals from each DIV sheets are transferred by macro to Sheet "Proof" to form the following table:<pre>
A C D E F
Jul 02 Aug 02 Sept 02 Oct 02

8 DIV001 100 120 130 140
9 DIV002 200 220 230 240
10 DIV003 300 320 330 340</pre>

Macros total and format the above table.

My problem is this. To copy and paste totals from each DIV worksheet to the Proof worksheet, each DIV is copied/pasted separately using the following code. This procedure is no real burden for 3 DIV worksheets but for say 10 DIV sheets there needs to be some looping code that does the bulk of the work. This looping code is the problem area.

Current procedure for each DIV:<pre>
'Get totals from DIV001
With Sheets("DIV001")
With .Range(.Cells(65536, 5).End(xlUp), .Cells(65536, :cool:.End(xlUp))
.Copy
With Sheets("Proof").Range("C8")
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End With
End With</pre>

The code for DIV002 and DIV003 is identical except for the first line: With Sheets("DIV002") and ("DIV003") respectively, and for the destination: Sheets("Proof").Range("C9") and ("C10") respectively.

Pseudocode:<pre>
i = Worksheets.Count
j = 2

For j = 2 To i 'loop through all sheets

If Left(ActiveSheet.Name, 3) = "DIV" Then
With .Range(.Cells(65536, 5).End(xlUp), .Cells(65536, :cool:.End(xlUp))

.Copy
With Sheets (Proof").Range("C8") 'for DIV001
With Sheets (Proof").Range("C9") 'for DIV002
With Sheets (Proof").Range("C10") 'for DIV003

.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False</pre>

Any ideas welcome.

Regards,

Mike
This message was edited by Ekim on 2002-10-02 22:32
This message was edited by Ekim on 2002-10-02 22:36
This message was edited by Ekim on 2002-10-02 22:39
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
An entire paragraph has disappeared from my original post (too long perhaps?).

Just above Pseudocode:

The code for DIV002 and DIV003 is identical except for the first line - With Sheets("DIV002") and ("DIV003") respectively, and for the destination - Sheets("Proof").Range("C9") and ("C10") respectively.

Regards,

Mike
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
You speak of "no real burden", could you define "real burden"?

What do you expect us to be concerned about, if you don't tell us?

Speed?
It crashes?

Thanks,
Steve
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Steve,

No crashes and no discernable speed problems.

Every time a new DIV sheet is added (say DIV004), the macro will have to be amended to include the following code:

<pre>

'Get totals from DIV004
With Sheets("DIV004")
With .Range(.Cells(65536, 5).End(xlUp), .Cells(65536, :cool:.End(xlUp))
.Copy
With Sheets("Proof").Range("C11") 'Change destination reference
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End With
End With
</pre>

Ditto for DIV005, 006 etc.

That's fine, assuming I'm around to do it. However, this seems inefficient. In particular, users will have to change the destination reference, otherwise the "Proof" worksheet will get messed up (assuming users know where the code is - the project has many macros in several modules).

IMO, the macro (all macros) should be independent of adding a new worksheet.

Regards,

Mike
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

Lets do some brain-storming on this:

A human being doing this would be looking for the source sheets, and they would be looking for any sheet that starts with "Div", correct? They would run through the list, and stop. They might not even worry about counting the sheets, or exactly know which sheet they are on.



In code, you could do a For Each...Next loop, for each worksheet.
That loop would test .Name

dim ws as variant
for each ws in worksheets
If Instr(1, Ws.Name,"Div",vbtextcompare)>0 then
DoIt
end if
Next


If the ordering DOES matter, you could do something like
For TheLoop = 1 to Worksheets.count
Test for "DIV" in Sheets(TheLoop).Name
If "DIV" found.....
Sheets(n).copy

.......


For this to work, you would just need to arrange the sheet tabs in the "correct" order.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
I think one issue is you have failed to define the problems in terms of patterns.

One pattern I see already: You are stepping through each sheet of the workbook, and copying from it if the name starts with "DIV".

The second pattern is, I think:

You are incrementing the destination column.

If that is so, then there is a relationship between the index of the sheet, and the column offset desired.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

Steve,

You are right about the pattern.

"For this to work, you would just need to arrange the sheet tabs in the "correct" order."

Yes, I recognize this. Users will be instructed to add any new DIV sheet at the end of existing ones.

Note that the use of the sheets names DIV001, DIV002 etc is for illustrative purposes. In reality, the sheets could be named DIV_Plastics, DIV_Mouldings, DiV_Fabrications etc. However, if a number is required to make the loop work, the sheet names can be changed to suit (but all will be prefixed with "DIV").

FYI, "DIV" is short for Division.

Regards,

Mike
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
If you order the sheets, with the "Div" sheets being the leftmost sheets, you can use the .Index property of the sheets to advance your column indexing.

Either as an .Offset(Rows, n + .Index) method, or as a .Cells(Rows, n + .Index) method; .Index can be used to move your column assignment to the right, as you step through the sheets.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Alternatively (but along similar lines), set a sheet up that contains the sheet names in say Col A. Then run a for each loop with the copying macro set within.

e.g ALL_SHEETS = Sheets("Sheet_Names").range("A1:A11")

For each WS in ALL_SHEETS

If WS = "" then EXIT SUB

then change the sheet reference in your macro to WS

Next WS
 

Forum statistics

Threads
1,144,274
Messages
5,723,442
Members
422,497
Latest member
dougy99

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