macro to control tabs (different worksheets)

  • Thread starter Thread starter JCO
  • Start date Start date
J

JCO

If I already have a macro that accomplishes a task on Worksheet 1, how to I
cycle through the same macro to accomplish the same exact task on Worksheet
2 and Worksheet 3 of the same Workbook.

Thanks
 
If I already have a macro that accomplishes a task on Worksheet 1,
how to I cycle through the same macro to accomplish the same exact
task on Worksheet 2 and Worksheet 3 of the same Workbook.

Thanks
Click to expand...

Please show your code.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Well the code is simple since I simple call the three sub routines shown
below:
So I want to call the same 3-routines for 3-worksheets in the same workbook

Sub FormatCharityTrackerReport()
RemoveComments&Columns
FormatReport
InsertHeader
End Sub
 
Hi,

Am Sat, 9 Nov 2013 14:15:30 -0600 schrieb JCO:
Well the code is simple since I simple call the three sub routines shown
below:
So I want to call the same 3-routines for 3-worksheets in the same workbook

Sub FormatCharityTrackerReport()
RemoveComments&Columns
FormatReport
InsertHeader
End Sub
Click to expand...

the important code is the code in your 3 procedures.
Into these procedures you have to change the code to:

For i = 1 to 3
With worksheet(i)
your code
end with
next

If you need more help please post the code of these 3 procedures


Regards
Claus B.
 
Ah! I gotch yak.
I was thinking I could encase the sub routines within the for loop, but now
I understand that I have to modify each of the routines. I have the info I
need, I believe.
Thanks
 
I thought I had it but I'm not sure what is wrong. I think because I'm
using "ActiveSheet" which the sheet 2 and 3 never go active so this code is
not working.
Code:

'Add proper text as header
For i = 1 To 3
With Worksheets(i)
With ActiveSheet.PageSetup
.LeftHeader = sReportHeader 'left header as text
.RightHeader = sCurDate 'right header as text
End With
End With
Next



"Claus Busch" wrote in message
Hi,

Am Sat, 9 Nov 2013 14:15:30 -0600 schrieb JCO:
Well the code is simple since I simple call the three sub routines shown
below:
So I want to call the same 3-routines for 3-worksheets in the same
workbook

Sub FormatCharityTrackerReport()
RemoveComments&Columns
FormatReport
InsertHeader
End Sub
Click to expand...

the important code is the code in your 3 procedures.
Into these procedures you have to change the code to:

For i = 1 to 3
With worksheet(i)
your code
end with
next

If you need more help please post the code of these 3 procedures


Regards
Claus B.
 
Disregard this. I figured it out by simply using the code below:

Sub FormatCharityTrackerReport()
Dim i As Integer

Application.PrintCommunication = False
For i = 1 To 3 'work done on 3-sheets
Worksheets(i).Activate
FormatReport
DateHeader
Next
Application.PrintCommunication = True
Worksheets(1).Activate 'reset back to sheet 1
End Sub


"JCO" wrote in message
I thought I had it but I'm not sure what is wrong. I think because I'm
using "ActiveSheet" which the sheet 2 and 3 never go active so this code is
not working.
Code:

'Add proper text as header
For i = 1 To 3
With Worksheets(i)
With ActiveSheet.PageSetup
.LeftHeader = sReportHeader 'left header as text
.RightHeader = sCurDate 'right header as text
End With
End With
Next



"Claus Busch" wrote in message
Hi,

Am Sat, 9 Nov 2013 14:15:30 -0600 schrieb JCO:
Well the code is simple since I simple call the three sub routines shown
below:
So I want to call the same 3-routines for 3-worksheets in the same
workbook

Sub FormatCharityTrackerReport()
RemoveComments&Columns
FormatReport
InsertHeader
End Sub
Click to expand...

the important code is the code in your 3 procedures.
Into these procedures you have to change the code to:

For i = 1 to 3
With worksheet(i)
your code
end with
next

If you need more help please post the code of these 3 procedures


Regards
Claus B.
 
Hi,

Am Mon, 11 Nov 2013 17:38:51 -0600 schrieb JCO:
'Add proper text as header
For i = 1 To 3
With Worksheets(i)
With ActiveSheet.PageSetup
.LeftHeader = sReportHeader 'left header as text
.RightHeader = sCurDate 'right header as text
End With
End With
Next
Click to expand...

you have to reference the PageSetup to the right sheets. Try:

For i = 1 To 3
With Worksheets(i)
With .PageSetup
.LeftHeader = sReportHeader 'left header as text
.RightHeader = sCurDate 'right header as text
End With
End With
Next


Regards
Claus B.
 
I knew it something like that.
Thanks

"Claus Busch" wrote in message
Hi,

Am Mon, 11 Nov 2013 17:38:51 -0600 schrieb JCO:
'Add proper text as header
For i = 1 To 3
With Worksheets(i)
With ActiveSheet.PageSetup
.LeftHeader = sReportHeader 'left header as text
.RightHeader = sCurDate 'right header as text
End With
End With
Next
Click to expand...

you have to reference the PageSetup to the right sheets. Try:

For i = 1 To 3
With Worksheets(i)
With .PageSetup
.LeftHeader = sReportHeader 'left header as text
.RightHeader = sCurDate 'right header as text
End With
End With
Next


Regards
Claus B.
 
Back
Top