Hello Paul,
A biggggg thank you for your help. I had actually completely forgotten that
there was a hidden sheet at all and i kept thinking my code was not going
beyond the active sheet.
That's why we need an expert from time to time. :)
Thanks for the tip regarding last active column and help me improve the code.
It works wonderfully now. :) :)
Thanks and Regards,
Gargee Singh
Date: Tue, 18 Aug 2015 12:14:58 +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
"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
Hello Paul
Thanks again for your help.
I had tried a similar code but couldn’t make It work because it works on
the current active sheet but not on the other sheets , so I thought was doing
something wrong.
The sample file is attached herewith along with the codes. For sampling I have
added only 3 sheets but we will have multiple centre sheets close to 80 /90 at
a time and once we run the code I want it to run on all sheets and move data
based 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
Hello Experts,
I have this code which works fine except that I am not able to repeat in
on 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 all
the 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.PasteSpecial
Paste:=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.
--
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.
