|
Selecting multiple tabs in VB
I have a spreadsheet with about ten different tabs. I am writing a macro
that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
You can select several sheets at once with something like:
Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Thanks, that helps. But I don't want to tell the macro which sheets to
select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Dawn
Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Yes, this is how I have it now. In the ['Your code here] section I have it
print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Shawn
I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Shawn
Change those "Or" that I gave you in my last code to "And". Otto "Otto Moehrbach" wrote in message ... Shawn I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
I understand how to do an If Statement and I will have to wrap it in a Do
Loop. This is a draft of what I have in mind Do Select the next tab If total < 0 then Add to WorkGroup (This is what I dont understand) End If Loop 10 time Right now I print at the €śAdd to WorkGroup€ť and this is causing problems. I want the end result outside of the above loop to be a WorkGroup of all the tabs on the spreadsheet that has values in their totals so they can all be printed at once. I do appreciate the responses though. Thanks, Shawn "Otto Moehrbach" wrote: Shawn Change those "Or" that I gave you in my last code to "And". Otto "Otto Moehrbach" wrote in message ... Shawn I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
OK I got the code below to work, but it still doesn't do what I want. The
two changes to the right is what I need to work - and it doesn't. The code to the left would work if I knew how many tabs would be printed, but I don't which is why I need something simular to the code to the Right. Sub Macro1() Dim Sht1 Dim Sht2 Dim Sht3 Dim TheTab Dim cnt Sheets("SUMMARY").Select Application.Goto Reference:="TabNames" TheTab = ActiveCell.Value Sheets(TheTab).Select Range("N111").Select If ActiveCell.Value < 0 Then Sht1 = TheTab End If Sheets("SUMMARY").Select ActiveCell.Offset(1, 0).Select TheTab = ActiveCell.Value Sheets(TheTab).Select Range("N111").Select If ActiveCell.Value < 0 Then Sht2 = TheTab ' Sht2 = Sht1 + ", " + TheTab End If Sheets(Array(Sht1, Sht2)).Select ' Sheets(Array(Sht2)).Select Sheets(Sht1).Activate End Sub "Shawn777" wrote: I understand how to do an If Statement and I will have to wrap it in a Do Loop. This is a draft of what I have in mind Do Select the next tab If total < 0 then Add to WorkGroup (This is what I dont understand) End If Loop 10 time Right now I print at the €śAdd to WorkGroup€ť and this is causing problems. I want the end result outside of the above loop to be a WorkGroup of all the tabs on the spreadsheet that has values in their totals so they can all be printed at once. I do appreciate the responses though. Thanks, Shawn "Otto Moehrbach" wrote: Shawn Change those "Or" that I gave you in my last code to "And". Otto "Otto Moehrbach" wrote in message ... Shawn I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Shawn
Look at this macro. Maybe this is what you want. This macro loops through all the sheets, checks for A1<0 and if the criteria is met, it adds that sheet name to an array. After the looping is done, it selects the array (as a group). Post back if you need more. Otto Sub SetupSheetArray() Dim ws As Worksheet Dim ShtArray() As String 'The array you want to set up Dim c As Long ReDim ShtArray(1 To 1000) c = 1 For Each ws In ActiveWorkbook.Worksheets With ws If .Range("A1") < 0 Then ShtArray(c) = ws.Name c = c + 1 End If End With Next ws ReDim Preserve ShtArray(1 To c) Sheets(ShtArray()).Select End Sub "Shawn777" wrote in message ... I understand how to do an If Statement and I will have to wrap it in a Do Loop. This is a draft of what I have in mind Do Select the next tab If total < 0 then Add to WorkGroup (This is what I don't understand) End If Loop 10 time Right now I print at the "Add to WorkGroup" and this is causing problems. I want the end result outside of the above loop to be a WorkGroup of all the tabs on the spreadsheet that has values in their totals so they can all be printed at once. I do appreciate the responses though. Thanks, Shawn "Otto Moehrbach" wrote: Shawn Change those "Or" that I gave you in my last code to "And". Otto "Otto Moehrbach" wrote in message ... Shawn I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Shawn,
Try it this way: Sub test() Dim ws As Object, i As Long, arr() i = 0 For Each ws In Sheets If ws.Range("N111") 0 Then ReDim Preserve arr(0 To i) arr(i) = ws.Name i = i + 1 End If Next If Sheets.Count 1 Then Sheets(arr).Select End If End Sub -- Dan On Dec 19, 8:58 am, Shawn777 wrote: OK I got the code below to work, but it still doesn't do what I want. The two changes to the right is what I need to work - and it doesn't. The code to the left would work if I knew how many tabs would be printed, but I don't which is why I need something simular to the code to the Right. Sub Macro1() Dim Sht1 Dim Sht2 Dim Sht3 Dim TheTab Dim cnt Sheets("SUMMARY").Select Application.Goto Reference:="TabNames" TheTab = ActiveCell.Value Sheets(TheTab).Select Range("N111").Select If ActiveCell.Value < 0 Then Sht1 = TheTab End If Sheets("SUMMARY").Select ActiveCell.Offset(1, 0).Select TheTab = ActiveCell.Value Sheets(TheTab).Select Range("N111").Select If ActiveCell.Value < 0 Then Sht2 = TheTab ' Sht2 = Sht1 + ", " + TheTab End If Sheets(Array(Sht1, Sht2)).Select ' Sheets(Array(Sht2)).Select Sheets(Sht1).Activate End Sub "Shawn777" wrote: I understand how to do an If Statement and I will have to wrap it in a Do Loop. This is a draft of what I have in mind Do Select the next tab If total < 0 then Add to WorkGroup (This is what I don't understand) End If Loop 10 time Right now I print at the "Add to WorkGroup" and this is causing problems. I want the end result outside of the above loop to be a WorkGroup of all the tabs on the spreadsheet that has values in their totals so they can all be printed at once. I do appreciate the responses though. Thanks, Shawn "Otto Moehrbach" wrote: Shawn Change those "Or" that I gave you in my last code to "And". Otto "Otto Moehrbach" wrote in message ... Shawn I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero.- Hide quoted text - - Show quoted text - |
Selecting multiple tabs in VB
Awesome, that is way above my ability €“ I would have never figured that out.
Thank You ALL Shawn "Otto Moehrbach" wrote: Shawn Look at this macro. Maybe this is what you want. This macro loops through all the sheets, checks for A1<0 and if the criteria is met, it adds that sheet name to an array. After the looping is done, it selects the array (as a group). Post back if you need more. Otto Sub SetupSheetArray() Dim ws As Worksheet Dim ShtArray() As String 'The array you want to set up Dim c As Long ReDim ShtArray(1 To 1000) c = 1 For Each ws In ActiveWorkbook.Worksheets With ws If .Range("A1") < 0 Then ShtArray(c) = ws.Name c = c + 1 End If End With Next ws ReDim Preserve ShtArray(1 To c) Sheets(ShtArray()).Select End Sub "Shawn777" wrote in message ... I understand how to do an If Statement and I will have to wrap it in a Do Loop. This is a draft of what I have in mind Do Select the next tab If total < 0 then Add to WorkGroup (This is what I don't understand) End If Loop 10 time Right now I print at the "Add to WorkGroup" and this is causing problems. I want the end result outside of the above loop to be a WorkGroup of all the tabs on the spreadsheet that has values in their totals so they can all be printed at once. I do appreciate the responses though. Thanks, Shawn "Otto Moehrbach" wrote: Shawn Change those "Or" that I gave you in my last code to "And". Otto "Otto Moehrbach" wrote in message ... Shawn I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero. |
Selecting multiple tabs in VB
Awesome, that is way above my ability €“ I would have never figured that out.
Thank You ALL Shawn "Dan R." wrote: Shawn, Try it this way: Sub test() Dim ws As Object, i As Long, arr() i = 0 For Each ws In Sheets If ws.Range("N111") 0 Then ReDim Preserve arr(0 To i) arr(i) = ws.Name i = i + 1 End If Next If Sheets.Count 1 Then Sheets(arr).Select End If End Sub -- Dan On Dec 19, 8:58 am, Shawn777 wrote: OK I got the code below to work, but it still doesn't do what I want. The two changes to the right is what I need to work - and it doesn't. The code to the left would work if I knew how many tabs would be printed, but I don't which is why I need something simular to the code to the Right. Sub Macro1() Dim Sht1 Dim Sht2 Dim Sht3 Dim TheTab Dim cnt Sheets("SUMMARY").Select Application.Goto Reference:="TabNames" TheTab = ActiveCell.Value Sheets(TheTab).Select Range("N111").Select If ActiveCell.Value < 0 Then Sht1 = TheTab End If Sheets("SUMMARY").Select ActiveCell.Offset(1, 0).Select TheTab = ActiveCell.Value Sheets(TheTab).Select Range("N111").Select If ActiveCell.Value < 0 Then Sht2 = TheTab ' Sht2 = Sht1 + ", " + TheTab End If Sheets(Array(Sht1, Sht2)).Select ' Sheets(Array(Sht2)).Select Sheets(Sht1).Activate End Sub "Shawn777" wrote: I understand how to do an If Statement and I will have to wrap it in a Do Loop. This is a draft of what I have in mind Do Select the next tab If total < 0 then Add to WorkGroup (This is what I don't understand) End If Loop 10 time Right now I print at the "Add to WorkGroup" and this is causing problems. I want the end result outside of the above loop to be a WorkGroup of all the tabs on the spreadsheet that has values in their totals so they can all be printed at once. I do appreciate the responses though. Thanks, Shawn "Otto Moehrbach" wrote: Shawn Change those "Or" that I gave you in my last code to "And". Otto "Otto Moehrbach" wrote in message ... Shawn I don't understand what you want that the code I gave you doesn't do. With the code I gave you, you can do whatever you want with each sheet in the workbook. Perhaps you have some sheets that you want to exclude from the "Your code here" part. If that's the case, you can easily exclude individual sheets with an IF statement like: If ws.Name<"This" Or ws.Name<"That" Or ws.Name<"The Other" Then 'Your code here" End If Post back with more detail about what you want that this code doesn't do for you. HTH Otto "Shawn777" wrote in message ... Yes, this is how I have it now. In the ['Your code here] section I have it print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select] in Gary's Student's reply. Then I can print the whole workbook at once - which is what I want done. So how do I combine Otto's and Gary's Student's help? "Otto Moehrbach" wrote: Dawn Something like this will loop through all the sheets in the workbook. I put in an IF statement to check some value in each sheet and if the criteria is met, your code will run. Post back if you need more. HTH Otto Sub DoEachSht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("C10").Value 10 Then 'Your code here End If Next ws End Sub "Shawn777" wrote in message ... Thanks, that helps. But I don't want to tell the macro which sheets to select, I want the marco to figure that out itself. Even the number of sheet to print will change from job to job. Some could print just one of the ten and others will print all ten. So can I get one name to equal ("Sheet1,","Sheet2")? Or is there a better way of doing this? "Gary''s Student" wrote: You can select several sheets at once with something like: Sheets(Array("Sheet1", "Sheet2")).Select -- Gary''s Student - gsnu200761 "Shawn777" wrote: I have a spreadsheet with about ten different tabs. I am writing a macro that will print all of these tabs that have data in them. I can write an if statement asking if the total doesn't equal 0 then print. When I do this each tab is printed out one at a time. This won't alow the page numbers in the footer to work and in our office printing these tabs separately can cause problem at the printer - since many people are using the printer at once. What I don't know how to write is to "select a workgroup" (select multiple tabs at once) of tabs that totals don't equal zero.- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 01:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com