Hello,
Would it be possible not to run a macro in specific worksheet?? I run a macro with the shortcut CTRL + P and I need to have that macro to be deactivated on certain worksheets (the worksheet names are "RENTCU1", "RENTCU2", "RENTCU3", "RENTCU4".
Thanks,
Graig
put an activesheet.name check at the start of the macro.
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
Thanks but I need to have it work the other way around. I would like to specify the worksheets I do not want to macro to be run.
Does this macro run on multiple worksheets when you call it or only on the active sheet?
If posting code please use code tags, see here.
can you post your macro? it is hard to know how to help with the code if we don't know what we are helping on. Do you reference the worksheet in your macro? Does the macro loop through all sheets?
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
Sure!
My macro is that one:
The worksheets that I do not want that macro o be run are: "RENTCU1", "RENTCU2", "RENTCU3", "RENTCU4"
Sub Macro1() Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long Set sh = ActiveSheet sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate breaks_count = sh.HPageBreaks.Count For i = 1 To breaks_count If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng) Next If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous 'PRINT VERSION ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End Sub
Many Thanks in advance.
Graig
Sure!
My macro is that one:
The worksheets that I do not want that macro o be run are: "RENTCU1", "RENTCU2", "RENTCU3", "RENTCU4"Sub Macro1() Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long Set sh = ActiveSheet sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate breaks_count = sh.HPageBreaks.Count For i = 1 To breaks_count If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng) Next If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous 'PRINT VERSION ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End Sub
Many Thanks in advance.
Graig
Yes, thats what i was getting at, in the 1st line of macro have
if activesheet.name<>"Barred Sheet1" and activesheet.name<>"Barred Sheet2" then your macro end if
Hi, graiggoriz,
maybe add this to ThisWorkbook and see if it works as expected:
Ciao,Private Sub Workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "RENTCU1", "RENTCU2", "RENTCU3", "RENTCU4" Cancel = True Case Else End Select End Sub
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Hello Holger,
Are you saying that I need to *** that code in the 4 worksheets? (right click on the tab; view code. and add the code?
If so, I already have the below code in each worksheet. If you meant to add the code differently can you please provide me the step? I don't know how to do that...
Many Thanks,Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In Worksheets If ws.Name <> "CURRENT1" Then ws.Visible = xlSheetVeryHidden Next ws End Sub
Graig
I think this will do what you want.
Sub Macro1a() Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long Set sh = ActiveSheet If Not (sh.Name Like "RENTCU[1234]") Then sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate breaks_count = sh.HPageBreaks.Count For i = 1 To breaks_count If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng) Next If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous 'PRINT VERSION ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End If End Sub
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Hey mikerickson!! You got it!! Very good.
I only have on problem with the printing part as if I cancel the printing I see the macro and in the file I use there is password! Would you know if the below macro can be enhanced and give me a pop up "would you like to print that document" and putting a condition that if "no" then it ends the macro instead of entering in debug mode! In the end I need to print that document as pdf but I did not know how to do that so I recorded a macro (CTRL + P)
Do you think you could help me with that matter?
Many Thanks,'PRINT VERSION ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False
Graig
graig, change it to:
'PRINT VERSION toprint = MsgBox("Would you like to print?", vbYesNo) If toprint = vbYes Then ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End If
Hello,
Thanks to all! I have compiled the macros and got that:
I have the following error:Sub PRIINT() ActiveSheet.Unprotect Password:="p@ssw0rd!" Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long Set sh = ActiveSheet 'ADD NEW LINE FOR NOT RUNNING THE MACRO IN CASE SHEET EQUAL TO CURRENT1,2,3,4,5,6: If Not (sh.Name Like "CURRENT[1234]") Then sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate breaks_count = sh.HPageBreaks.Count For i = 1 To breaks_count If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng) Next If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous ActiveSheet.Protect Password:="p@ssw0rd!" 'PRINT VERSION toprint = MsgBox("Would you like to print?", vbYesNo) If toprint = vbYes Then ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End If End Sub
Compile error:
Block If without End If
Can you please help me fixing that error?
Many thanks,
Graig
Hi, Graig,
I don´t get the reason for repeating the post from 08:50 PM at 10:15 PM (I know there have been Forum issues and my post wasn´t properly displayed nor passed to my count nor was displayed on the survey of the Forum). Did you try my suggestion?
Ciao,
Holger
Hello,
Thanks to all! I have compiled the macros and got that:
I have the following error:Sub PRIINT() ActiveSheet.Unprotect Password:="p@ssw0rd!" Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long Set sh = ActiveSheet 'ADD NEW LINE FOR NOT RUNNING THE MACRO IN CASE SHEET EQUAL TO CURRENT1,2,3,4,5,6: If Not (sh.Name Like "CURRENT[1234]") Then sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate breaks_count = sh.HPageBreaks.Count For i = 1 To breaks_count If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng) Next If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous ActiveSheet.Protect Password:="p@ssw0rd!" 'PRINT VERSION toprint = MsgBox("Would you like to print?", vbYesNo) If toprint = vbYes Then ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End If End Sub
Compile error:
Block If without End If
Can you please help me fixing that error?
Many thanks,
Graig
Hi, graiggoriz,
indention could be one way to find the error, this is untested (I shifted some of the lines):
Ciao,Sub PRIINT() Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long Set sh = ActiveSheet 'ADD NEW LINE FOR NOT RUNNING THE MACRO IN CASE SHEET EQUAL TO CURRENT1,2,3,4,5,6: If Not (sh.Name Like "CURRENT[1234]") Then sh.Unprotect Password:="p@ssw0rd!" sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate breaks_count = sh.HPageBreaks.Count For i = 1 To breaks_count If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng) End If Next i If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous 'PRINT VERSION If MsgBox("Would you like to print?", vbYesNo) = vbYes Then sh.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False End If sh.Protect Password:="p@ssw0rd!" End If Set sh = Nothing End Sub
Holger
your 'print version section has an if, no end if. The end if near the bottom applies to "if not" at the top. I suggest adding an "End If" just before "End Sub"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
graiggoriz 
Register To Reply
Bookmarks