I would think that you could have an inputbox (or even a userform) that prompts
the user for the worksheets to process. But then you'll have to validate their
entry and if you find a mistake, they have to fix it before you start.

You could have userfrom with a list of worksheets and have them select from that
list.

Or (I think simplest to implement, more difficult to train!), you could have the
user select the worksheet tabs that they want processed.

Click on the first and ctrl-click on the subsequent worksheet tab.

Then cycle through those selected sheets:

Option Explicit
Sub Macro2a()

Dim myRng As Range
Dim Wks As Worksheet

For Each Wks In ActiveWindow.SelectedSheets
With Wks
.Range("S3").Value = "GSTRate"
With .Columns("S:S")
.Copy
.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
.TextToColumns Destination:=.Range("S1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)

Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If myRng Is Nothing Then
'do nothing--no blanks!
Else
myRng.FormulaR1C1 = "=R[1]C"
End If
End With
.UsedRange.Columns.Hidden = False

Set myRng = .Cells(.Rows.Count, "S").End(xlUp).Offset(0, -18)
Set myRng = .Range(myRng, myRng.End(xlUp))

.Range("A4:M4").Copy _
Destination:=myRng

End With
Next Wks

ActiveWindow.SelectedSheets(1).Select

Application.CutCopyMode = False

End Sub

This line:
ActiveWindow.SelectedSheets(1).Select
should select the first sheet in the selected sheets. When worksheets are
grouped, anything you do to one will be done to all. So it could be dangerous
to leave them selected.



Hidaya wrote:

Hi,
I have an Excel Workbook with 32 worksheets in it (31 days & summary). I
have the following macro and would like to run it in some of the worksheets
(e.g. from day1 to day4, day15 to day20 and so on) . Is there a way to do it
at one go rather than to select the worksheet one by one? Maybe create an
input box to enter the date range? I dont have much knowledge of VBA and
would appreciate any help. Thank you.

' Macro2 Macro(cntl+shift+i)

'
Columns("S:S").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("S3").Value = "GSTRate"
Columns("S:S").Select
Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1)
Selection.SpecialCells(xlCellTypeBlanks).Select
Range("S2").Activate
Selection.FormulaR1C1 = "=R[1]C"
Cells.Select
Selection.EntireColumn.Hidden = False
Range("A4:M4").Select
Selection.Copy
Range("S1000").End(xlUp).Select
ActiveCell.Offset(0, -18).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

--

Dave Peterson