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

for each ws in worksheets
your code
next ws

BTW. Your code could be more efficient by getting rid of selections. Example
========
Columns("s").Value = Columns("s").Value

Columns("S:S").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
==================

There may be easier ways to handle this but my suggestion is to make separate
macros to handle the different tasks. Using the macro you have already made
you can place a "Sheets("day1").Select" command at the beginning, then
copy/paste the entire macro to the end (before the END SUB command), changing
the "Sheets("day1").Select" line to day2 on the second section, then do it
again for day3 and day4. (I did this for you as day1 and day2 on your macro
attachment) Save that as whatever name works for you. Then copy that entire
macro but change the sheets to day15 through day20, or whatever days you
need. You will wind up with 3 or 4 different macros but if you make a
shortcut button for each one they are easy to use.

Hope it makes sense.

Squeaky

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

'
Sheets("day1").Select
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

Sheets("day2").Select
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

Hi Don ,
When I use for... each ws in worksheet, there is an error "Run time error
1004.No cells were found." at this code.

Selection.SpecialCells(xlCellTypeBlanks).Select

And it doesn't move to the next worksheet. Is there a way to make the macro
run only on selected worksheets only (e.g WS1 to WS10, WS13 to WS 15 etc
....)?. Thank you.



"Don Guillett" wrote:

for each ws in worksheets
your code
next ws

BTW. Your code could be more efficient by getting rid of selections. Example
========
Columns("s").Value = Columns("s").Value

Columns("S:S").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
==================

try this idea. modify to suit your needs to exclude certain ws. What do you
want to do with the blank cells?

for each ws in worksheets
if ws.name < "ws14" then
ws.SpecialCells(xlCellTypeBlanks).do something withOUT selecting
end if
next ws
========
Here's another idea
Dim MySh(1 To 10)
For i = 1 To 10
MySh(i) = "Sheet" & i
Next i
Sheets(MySh).dosomething
===========
repeat for 13 to 15, etc

--
Don Guillett
SalesAid Software

"Hidaya" wrote in message
...
Hi Don ,
When I use for... each ws in worksheet, there is an error "Run time error
1004.No cells were found." at this code.

Selection.SpecialCells(xlCellTypeBlanks).Select

And it doesn't move to the next worksheet. Is there a way to make the
macro
run only on selected worksheets only (e.g WS1 to WS10, WS13 to WS 15 etc
...)?. Thank you.



"Don Guillett" wrote:

for each ws in worksheets
your code
next ws

BTW. Your code could be more efficient by getting rid of selections.
Example
========
Columns("s").Value = Columns("s").Value

Columns("S:S").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
==================

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