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