Looping through worksheets with name change

  • Thread starter Thread starter pwk
  • Start date Start date
P

pwk

I have this macro that copies data to new worksheet (below). I would
like to programmably change the day of the week from Sat to Mon, Tue,
Wed, (no Sun) etc. and use a loop so I don’t have to rewrite macro 5
more times. I have asked the primary question on my previous posting
but, am posting this question additionally, it being a new query. Any
help would be appreciated, especially on Saturday.

Sub CombineSat3()

On Error GoTo EndOfMacro

Windows("Consolidated Worksheet.xls").Activate
For x = 1 To 12
Workbooks("combined sheets.xls").Sheets("Sat (" & x & ")").Range
("F6:F11").Copy
Range("AC4").Offset(, x).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _ SkipBlanks :=False, Transpose:=False

Next

EndOfMacro:
Exit Sub

End Sub
 
Modify this idea to suit
========
Option Explicit

Sub foreachday()
Dim days, d
Dim i As Double
days = Array("Sat", "Mon")'fill in
For Each d In days
On Error Resume Next
For i = 1 To 3
MsgBox Sheets(d & " (" & i & ")").Range("a1")
Next i
Next d
End Sub
========
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have this macro that copies data to new worksheet (below). I would
like to programmably change the day of the week from Sat to Mon, Tue,
Wed, (no Sun) etc. and use a loop so I don’t have to rewrite macro 5
more times. I have asked the primary question on my previous posting
but, am posting this question additionally, it being a new query. Any
help would be appreciated, especially on Saturday.

Sub CombineSat3()

On Error GoTo EndOfMacro

Windows("Consolidated Worksheet.xls").Activate
For x = 1 To 12
Workbooks("combined sheets.xls").Sheets("Sat (" & x & ")").Range
("F6:F11").Copy
Range("AC4").Offset(, x).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _ SkipBlanks :=False, Transpose:=False

Next

EndOfMacro:
Exit Sub

End Sub
 
Back
Top