Need VB Print Macro in Excel 97 2

[jdegeorge]

[tt]I have a 3-tabbed worksheet for which I want to create a few print macros. 1 macro each to print the 3 tabs individually and then a 4th macro to print all 3. I recorded a macro to print one of the tabs and this is what it gave me:

Sub Print_Gaps()
Range("A1:J80&quot.Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$80"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

I thought that the keystrokes would have been captured rather than the cell references at the time. For example, the keystrokes were: [HOME] [UP ARROW] [UP ARROW]...etc. to capture the desired range and the macro saved the actual cell references. Users can add and delete rows of information so "A1:J80" won't always be the range.

First question: Can I set up a macro to record the actual keystrokes rather than cell references?

Second question: Can I instruct the macro to work on a particular or multiple tabs (all with the same format)?

Although I've used VB in Access, this is my first attempt at using it in Excel and would really appreciate the help. Thanks in advance! [/tt]

Jim DeGeorge

 

[Loomah]

Hi
I'm not really sure how to answer your questions but I'll try

1) This is the one I'm not sure how to answer so I'll just give a few options
You need a dynamic range each time you run your print macro(s) so you need to find the area to print. If this area is all the data on the sheet you could try any of the following:-
range("A1&quot.currentregion (cells MUST be contiguous)
sheets(1).usedrange (not 100% reliable, see below)
there are two FAQs in the VBA forum on finding the last used cell/true used range. This is one of them (mine!)faq707-2112 Adapt this code or that in xlbo's faq to get your print area.

2) Yes! Examples below
This demonstrates how you could work on each sheet in turn. apparently it's not wise to traverse arrays using For..Each but this still works!

Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    ws.Activate
    MsgBox ws.Name
Next

Print out all these sheets in one hit

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Hope this helps(at least a little!)
Happy Friday
;-)



If a man says something and there are no women there to hear him, is he still wrong?

 

[jdegeorge]

[tt]Loomah

Thanks for the quick response. The first FAQ worked perfectly to identify the used rows on each tab in the worksheet. You're close to understanding my 2nd question.

In your response to the 2nd question you gave me 2 macros. The 2nd is to print out all 3 worksheets at one time. The first one doesn't seem to be what I need. I want to create 3 macros...one to print tab1, one to print tab2 and one to print tab3. If I'm understanding your 1st macro, it still prints out all 3 tabs. Could I simply create 3 macros based on this example with only 1 tab in the array, or does an array have to have more than one element?

Jim DeGeorge

 

[Loomah]

Slow down!!
You already have the macro to print one sheet in your own posting. All you need to do is add the line
Worksheets("Sheet1&quot.activate
at the beginning depending on where the macro will be run from.

Then instead of hardcoding the print area try using something like this using variable created from finding the last cell

.printarea = range(cells(1,1),cells(lastRow,lastCol).address

I was under the impression that you wanted to work on each sheet in turn. Hence the first bit of code, my misunderstanding!

To answer your question in passing, I don't think it matters if an array ony has 1 element but then it wouldn't be much of an array if it did have only 1 element!

Hope this helps
happy fiday
;-)


If a man says something and there are no women there to hear him, is he still wrong?

 

[pappy1271]

I want to make sure I understand you first. You have an Excel Workbook that has 3 worksheets? You want to print the entire contents of each worksheet? 3 macros to print each separately, and one to print all 3 at once. If this is correct this is what I would do.

First of all, if you are going to print the entire contents of the worksheet you will not need to make any selections. You could use something like this for each worksheet:

Sub PSheet1()

Worksheets("Sheet1&quot.Activate
Selection.PrintOut Copies:=1, Collate:=True
End Sub

Sub PSheet2()

Worksheets("Sheet2&quot.Activate
Selection.PrintOut Copies:=1, Collate:=True
End Sub

Sub PSheet3()

Worksheets("Sheet3&quot.Activate
Selection.PrintOut Copies:=1, Collate:=True
End Sub

Where "Sheet#" = the actual names of your worksheets. You could then assign each macro to its own buttons. For the fourth button to print all worksheets you could use:

Sub PSheetAll()

Sheets(Array("Sheet1", "Sheet2", "Sheet3&quot).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub
Then assign that macro to its own button.

Hope this helps
pappy

 

[jdegeorge]

Pappy/Loomah

Enjoy the stars! I actually wound up using a combination of both your responses. From the Macro Menu (Alt+F8), the macros run okay. Only certain macros run from the shortcut keys I created (i.e. CTRL+u). Others don't work using these keys. I noticed a patttern...Macros that are just named like "Print Gaps" work with the shortcut keys. Macros named like "CSC Integration GAP Analysis.xls'!FindUsedRange.FindUsedRange" don't work using the shortcuts.

First, I never assigned these names so I don't know how that happened. How can I rename that last one back to "FundUsedRange"?

Jim DeGeorge

 

[jdegeorge]

I added buttons and assigned macros to each of them. That actually works better for me than does the shortcut key method. I'm all set. Thanks again for your help!

Jim DeGeorge