PDA

View Full Version : Solved: Copy a range from the fourth sheet on



jolivanes
04-05-2009, 05:25 AM
In a tracking workbook, I have anywhere from 5 to 60+ sheets, job time depending.
At the end of the job I want to summarize the entries from cells C10 and D10 of
all sheets except the first three sheets.
Cell C10 has a text string and cell D10 is an amount relating to Cell C10.

I found this but I need to change it so it starts copying from sheet4.



Sub Combine()
Dim destSH As Worksheet, sh As Worksheet
Dim rw As Long

Application.ScreenUpdating = False

Set destSH = Worksheets.Add(After:=Worksheets(Worksheets.Count))
destSH.Name = "Summary"

rw = 10

For Each sh In Worksheets
If sh.Name <> destSH.Name Then
sh.Range("B3:C3").Copy
With destSH.Cells(rw, 1)
.PasteSpecial Paste:=xlPasteValues
End With

rw = rw + 1

End If

Next sh

Application.ScreenUpdating = True

End Sub


Could someone please assist me with this?

Thanks and Regards
John

xld
04-05-2009, 07:06 AM
For shNum = 4 To Worksheets.Count

Set sh = Worksheets(shNum)
If sh.Name <> destSH.Name Then
sh.Range("B3:C3").Copy
With destSH.Cells(rw, 1)
.PasteSpecial Paste:=xlPasteValues
End With

rw = rw + 1
End If
Next shNum

jolivanes
04-05-2009, 08:49 AM
Hi Bob.

Works great.

Thank you very much.

John