| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Code Help
Afternoon,
Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out certain sheets based on whether there is data in it or not, (the value in cell AA273 is either 1 or 2). I'm trying to get the code to cycle through the sheets automatically to print out the set ranges. The code prints ok, but it will print sheet that I am currently on several times rather than printing 4,5,6...11. In order to save some trees I have shortened the range on the code I'm using as per below to try and only print a few sheets until i get it to work. This code has printed twice sheet 11 and twice sheet 4 as I was on these sheets when i ran the code. How do I get it to print each sheet once rather than one sheet loads of times? Thanks LiAD ub PrintSheetsMacro() ' Dim X As Long For X = 10 To 11 Worksheets(X).Rows("111:214").EntireRow.Hidden = False If [AA273].Value = 2 Then Rows("267:274").EntireRow.Hidden = False Range("C266:P325").Select ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Rows("267:274").Select Selection.EntireRow.Hidden = True Range("B10").Select Else Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Range("B10").Select End If Next End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code Help
Change
ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True to Worksheets(X).PageSetup.PrintArea = "$C$266:$P$325" etc "LiAD" wrote: Afternoon, Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out certain sheets based on whether there is data in it or not, (the value in cell AA273 is either 1 or 2). I'm trying to get the code to cycle through the sheets automatically to print out the set ranges. The code prints ok, but it will print sheet that I am currently on several times rather than printing 4,5,6...11. In order to save some trees I have shortened the range on the code I'm using as per below to try and only print a few sheets until i get it to work. This code has printed twice sheet 11 and twice sheet 4 as I was on these sheets when i ran the code. How do I get it to print each sheet once rather than one sheet loads of times? Thanks LiAD ub PrintSheetsMacro() ' Dim X As Long For X = 10 To 11 Worksheets(X).Rows("111:214").EntireRow.Hidden = False If [AA273].Value = 2 Then Rows("267:274").EntireRow.Hidden = False Range("C266:P325").Select ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Rows("267:274").Select Selection.EntireRow.Hidden = True Range("B10").Select Else Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Range("B10").Select End If Next End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code Help
Actually, I guess you want to print the earlier section first.
sub PrintSheetsMacroSAS() Dim X As double For X = 4 To 11 with Worksheets(X) .range("C206:P265").PrintOut 'AND If .range("AA273").Value = 2 Then .Rows("267:274").Hidden = False .Range("C266:P325").PrintOut .Rows("267:274").Hidden = True End If end with Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... You need to use WITH. Notice the dots placement. I think I read your desires properly. But, why are you un-hiding/hiding rows 111:214 when you don't seem to use them. You may want to delete those 2 lines. sub PrintSheetsMacroSAS() Dim X As double For X = 4 To 11 with Worksheets(X) .Rows("111:214").Hidden = False'??? If .range("AA273").Value = 2 Then .Rows("267:274").Hidden = False .Range("C266:P325").PrintOut .Rows("267:274").Hidden = True End If .range("C206:P265").PrintOut .rows("111:214").Hidden = True'??? end with Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiAD" wrote in message ... Afternoon, Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out certain sheets based on whether there is data in it or not, (the value in cell AA273 is either 1 or 2). I'm trying to get the code to cycle through the sheets automatically to print out the set ranges. The code prints ok, but it will print sheet that I am currently on several times rather than printing 4,5,6...11. In order to save some trees I have shortened the range on the code I'm using as per below to try and only a few sheets until i get it to work. This code has printed twice sheet 11 and twice sheet 4 as I was on these sheets when i ran the code. How do I get it to print each sheet once rather than one sheet loads of times? Thanks LiAD ub PrintSheetsMacro() ' Dim X As Long For X = 10 To 11 Worksheets(X).Rows("111:214").EntireRow.Hidden = False If [AA273].Value = 2 Then Rows("267:274").EntireRow.Hidden = False Range("C266:P325").Select ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Rows("267:274").Select Selection.EntireRow.Hidden = True Range("B10").Select Else Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Range("B10").Select End If Next End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code Help
Yeah seems backwards to hide/unhide but there are things that i need to hide
to make ti easier for the user to use the file but i need to print as the printed version goes to other people. Works perfect thanks a lot for your help "Don Guillett" wrote: Actually, I guess you want to print the earlier section first. sub PrintSheetsMacroSAS() Dim X As double For X = 4 To 11 with Worksheets(X) .range("C206:P265").PrintOut 'AND If .range("AA273").Value = 2 Then .Rows("267:274").Hidden = False .Range("C266:P325").PrintOut .Rows("267:274").Hidden = True End If end with Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... You need to use WITH. Notice the dots placement. I think I read your desires properly. But, why are you un-hiding/hiding rows 111:214 when you don't seem to use them. You may want to delete those 2 lines. sub PrintSheetsMacroSAS() Dim X As double For X = 4 To 11 with Worksheets(X) .Rows("111:214").Hidden = False'??? If .range("AA273").Value = 2 Then .Rows("267:274").Hidden = False .Range("C266:P325").PrintOut .Rows("267:274").Hidden = True End If .range("C206:P265").PrintOut .rows("111:214").Hidden = True'??? end with Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiAD" wrote in message ... Afternoon, Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out certain sheets based on whether there is data in it or not, (the value in cell AA273 is either 1 or 2). I'm trying to get the code to cycle through the sheets automatically to print out the set ranges. The code prints ok, but it will print sheet that I am currently on several times rather than printing 4,5,6...11. In order to save some trees I have shortened the range on the code I'm using as per below to try and only a few sheets until i get it to work. This code has printed twice sheet 11 and twice sheet 4 as I was on these sheets when i ran the code. How do I get it to print each sheet once rather than one sheet loads of times? Thanks LiAD ub PrintSheetsMacro() ' Dim X As Long For X = 10 To 11 Worksheets(X).Rows("111:214").EntireRow.Hidden = False If [AA273].Value = 2 Then Rows("267:274").EntireRow.Hidden = False Range("C266:P325").Select ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Rows("267:274").Select Selection.EntireRow.Hidden = True Range("B10").Select Else Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Range("B10").Select End If Next End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Print Code Help
Why unhideprinthide if there are NO selections for the user to even see
and you are not printing that section??? -- Don Guillett Microsoft MVP Excel SalesAid Software "LiAD" wrote in message ... Yeah seems backwards to hide/unhide but there are things that i need to hide to make ti easier for the user to use the file but i need to print as the printed version goes to other people. Works perfect thanks a lot for your help "Don Guillett" wrote: Actually, I guess you want to print the earlier section first. sub PrintSheetsMacroSAS() Dim X As double For X = 4 To 11 with Worksheets(X) .range("C206:P265").PrintOut 'AND If .range("AA273").Value = 2 Then .Rows("267:274").Hidden = False .Range("C266:P325").PrintOut .Rows("267:274").Hidden = True End If end with Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... You need to use WITH. Notice the dots placement. I think I read your desires properly. But, why are you un-hiding/hiding rows 111:214 when you don't seem to use them. You may want to delete those 2 lines. sub PrintSheetsMacroSAS() Dim X As double For X = 4 To 11 with Worksheets(X) .Rows("111:214").Hidden = False'??? If .range("AA273").Value = 2 Then .Rows("267:274").Hidden = False .Range("C266:P325").PrintOut .Rows("267:274").Hidden = True End If .range("C206:P265").PrintOut .rows("111:214").Hidden = True'??? end with Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiAD" wrote in message ... Afternoon, Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out certain sheets based on whether there is data in it or not, (the value in cell AA273 is either 1 or 2). I'm trying to get the code to cycle through the sheets automatically to print out the set ranges. The code prints ok, but it will print sheet that I am currently on several times rather than printing 4,5,6...11. In order to save some trees I have shortened the range on the code I'm using as per below to try and only a few sheets until i get it to work. This code has printed twice sheet 11 and twice sheet 4 as I was on these sheets when i ran the code. How do I get it to print each sheet once rather than one sheet loads of times? Thanks LiAD ub PrintSheetsMacro() ' Dim X As Long For X = 10 To 11 Worksheets(X).Rows("111:214").EntireRow.Hidden = False If [AA273].Value = 2 Then Rows("267:274").EntireRow.Hidden = False Range("C266:P325").Select ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Rows("267:274").Select Selection.EntireRow.Hidden = True Range("B10").Select Else Range("C206:P265").Select ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Rows("111:214").Select Selection.EntireRow.Hidden = True Range("B10").Select End If Next End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Print code | Excel Discussion (Misc queries) | |||
| Differentiate between 'Print' and 'Print Preview' in VBA code | Excel Programming | |||
| Code before Print but not Print Preview | Excel Programming | |||
| Need Code To Print From Code Modules | Excel Programming | |||
| Need Code To Print From Code Modules | Excel Programming | |||
Linear Mode
