I am trying to clean up some code where I have simplified what it does. (original code below)
Upon a macro button push, (on pg "s") a summary prints info on a series of cells from each of many sheets.
(see code below)
The code as written puts the info from each page (F71 through F85) summarized on the "s" page
on cells BX7 through BX 23.
Then the info from each page F90 - F107, summarizes on the "S" page starting at BX26.
I want to change this code, so I am only pulling only one section from each page. In this case, I would
be pulling info from each page F-71 through F101, to the summary page "S" and the info would show up
on the "s" page, at cells BX7, down through BX 67.
Can someone change / simplify this code for me? Every time I try to edit it, I just get errors.
Also, I am not very good at this code stuff, so hopefully someone can edit it for me.
Thanks in advance!!!! (I'm using excel 2007)
Sub copytoS()
Dim WS As Worksheet
Dim nrow As Long
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
nrow = 7
nrow2 = 43
Application.ScreenUpdating = False
Set rng4 = Range(Sheets("S").Cells(7, 76), Sheets("S").Cells(43, 76))
rng4.ClearContents
Set rng4 = Range(Sheets("S").Cells(26, 76), Sheets("S").Cells(55, 76))
rng4.ClearContents
For Each WS In ActiveWorkbook.Worksheets
If WS.Name <> "1" And WS.Name <> "2" And WS.Name <> "T" And WS.Name <> "S" Then
Set rng2 = WS.Range("F71", "F94")
For Each F In rng2
If IsError(F.Value) = False Then
If F.Value <> "" Then
F.Copy
Set rng3 = Range(Sheets("S").Cells(nrow, 76), Sheets("S").Cells(nrow, 76))
rng3.PasteSpecial Paste:=xlPasteValues
nrow = nrow + 1
End If
End If
Next F
End If
Next WS
For Each WS In ActiveWorkbook.Worksheets
If WS.Name <> "1" And WS.Name <> "2" And WS.Name <> "T" And WS.Name <> "S" Then
Set rng2 = WS.Range("F90", "F104")
For Each F In rng2
If IsError(F.Value) = False Then
If F.Value <> "" Then
F.Copy
Set rng3 = Range(Sheets("S").Cells(nrow2, 76), Sheets("S").Cells(nrow2, 76))
rng3.PasteSpecial Paste:=xlPasteValues
nrow2 = nrow2 + 1
End If
End If
Next F
End If
Next WS
Application.ScreenUpdating = True
End Sub
Bookmarks