Looping macro

[Ekim]

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, .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, .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

 

[Ekim]

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]

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]

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, .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]

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.copy

.......


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

 

[stevebausch]

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]

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]

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]

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