"Technically", it looks like you're not describing the problem accurately.The
problem isn't that it only works on the active sheet, the problem is that you
have a hidden sheet!
It just HAPPENS that in the list of sheets, you have:Summary ASummary BSummary
CCentre Acentre formats (original ) (which is hidden)Centre BCentre C
the Process_All macro skips the Summary sheets, and processes "Centre A"but
encounters an error when it tries to select the hidden sheet.
I simply added a test to see if the next sheet is visible.
also, In loops, it's a good idea to stop looking once you find what you're
looking for!"Exit For" will exit the loop once you've found the appropriate
value.
So, my resulting macros look like:-------------------------Option Explicit
Sub movedata()
Dim Lastcolumn As Integer, i, j As Integer, erow, ocell As Integer
Lastcolumn = Cells(Columns.Count, ActiveCell.Column).End(xlToRight).Column
For i = 7 To 34
If Cells(9, i) = 1 Then
Range(Cells(9, i), Cells(54, i + 34)).Select
Selection.Cut
Exit For
End If
Next i
For j = 7 To Lastcolumn
If Cells(8, j) = Cells(1, 5) Then
Cells(9, j).Select
ActiveSheet.Paste
Cells(19, j).Activate
ActiveCell.Value = 1
Exit For
End If
Next j
Columns("BY:BY").Select
Selection.Copy
Columns("G:AK").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
Sub Process_All()
Dim Sht
For Each Sht In Sheets
If ((Sht.Name <> "Summary A") _
And (Sht.Name <> "Summary B") _
And (Sht.Name <> "Summary C") _
) Then
If (Sht.Visible = True) Then
Sht.Select
Call movedata
End If
End If
Next Sht
End Sub
------------Also: I'm not sure what you are attempting to do with:
Lastcolumn = Cells(Columns.Count, ActiveCell.Column).End(xlToRight).Column
The Cells() method uses the form: Cells(row,column)You're using the number of
columns (16384) as the row numberand the column number of the currently
selected cell as the column.
Since your samples use less than 60 rows, I suspect that row 16384 will almost
always be blank.which means that using .end(xlToRight) should move to column
XFDwhich is column 16384.
You might as well simply set LastColumn = 16384
If instead, you're trying to find the column number of the last column with a
heading in row 8, you could use:
Lastcolumn = Cells(8, "G").End(xlToRight).Column
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: gargee singh <garge...@hotmail.com>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Sent: Tuesday, August 18, 2015 2:40 AM
Subject: RE: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
<!--#yiv1369811345 .yiv1369811345hmmessage
P{margin:0px;padding:0px;}#yiv1369811345
body.yiv1369811345hmmessage{font-size:12pt;font-family:Calibri;}-->Hello Paul
Thanks again for your help. I had tried a similar code but couldn’t make It
work because it works onthe current active sheet but not on the other sheets ,
so I thought was doingsomething wrong. The sample file is attached herewith
along with the codes. For sampling I haveadded only 3 sheets but we will have
multiple centre sheets close to 80 /90 ata time and once we run the code I want
it to run on all sheets and move databased on the month mentioned in E1 .
Thanks and Regards,
Gargee Singh
Date: Mon, 17 Aug 2015 11:44:25 +0000
From: schreiner_p...@att.net
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
Are you saying that you want the macro to repeat for all sheets?
If so, you need to set up a loop to process all sheets.
Using your macro, you can create a separate macro like:
Sub Process_All()
Dim Sht for each Sht in Sheets if ((sht.name <> "Summary A") _ and
(sht.name <> "Summary B") _ and (sht.name <> "Summary C") _ ) then
sht.select movedata end if next Sht
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: gargee singh <garge...@hotmail.com>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Sent: Monday, August 17, 2015 3:28 AM
Subject: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
<!--#yiv1369811345 .yiv1369811345ExternalClass #yiv1369811345ecxyiv9893916402
body.yiv1369811345ecxyiv9893916402hmmessage
{font-size:12pt;font-family:Calibri;}-->
Hello Experts, I have this code which works fine except that I am not able to
repeat inon all sheets. When I edit the code it doesn’t perform the same
routine. I have multiple sheets in a workbook and I want this code to run on
allthe sheets except the first 3 (named – Summary A, Summary B, Summary C) Sub
movedata() Dim Lastcolumn As Integer, i, j As Integer, erow, ocell As Integer
Lastcolumn = Cells(Columns.count, ActiveCell.Column).End(xlToRight).Column For
i = 7 To 34 If Cells(9, i) = 1 Then Range(Cells(9, i), Cells(54, i +
34)).Select Selection.Cut End If Next i For j = 7 To Lastcolumn If Cells(8, j)
= Cells(1, 5) Then Cells(9, j).Select ActiveSheet.Paste Cells(19, j).Activate
ActiveCell.Value = 1 End If Next j Columns("BY:BY").Select Selection.Copy
Columns("G:AK").Select Selection.PasteSpecialPaste:=xlPasteFormats,
Operation:=xlNone, _ SkipBlanks:=False,Transpose:=False End Sub
Thanks for the help in advance.
Thanks and Regards,
Gargee Singh
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
