Vba printing in Excel 2003

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:02 PM GMT

Question

QUESTION: I have this page in my workbook, there is a column of names. On the same page is a form. One name at a time needs to be copied to a field in the form so that the form can print and the next name copied and the form prints.


By using the macro recorder and my limited knowledge, I can create a macro that will copy the first name in the column paste special in the form, highlight the area of the form I want to print and print.

What I'm looking for is some code that will pick the next name in the column and copy it to the Same place on the form Until the list of names has been exhausted.

The number of names in column can very day today.

It can't see the formula that will place the name there.

I can't say that I have a good handle on VBA for applications, but I'm working on it.

I think it would be a loop, but then again that's why I'm asking the question.

Below is an example.

[code] [face=Courier New][color=darkblue]Sub[/color] Print_Daily_Clinical_Back_Side_New_1() [color=green]'[/color] ' Print_Daily_Clinical_Back_Side_1 Macro [color=green]' Macro recorded 12/12/2010 by km[/color] [color=green]'[/color]

'

   Application.ScreenUpdating = [color=darkblue]False[/color]
   CopyStatesButen                   [color=green]'Sorts[/color]
   Sort_32                           'Sorts other suff
   UnProtectWorkBook                 [color=green]'By Password[/color]
   ActiveSheet_Unprotect             'By Password
   Sheets("Daily Clinical Review").Visible = xlSheetVisible
   Sheets("Daily Clinical Review").Select
   [color=green]'Then do this.[/color]
   Range("U8").Select
   Application.CutCopyMode = [color=darkblue]False[/color]
   Selection.Copy
   Range("AX9").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=[color=darkblue]False[/color]
   [color=green]'Then do this.[/color]
   Range("V8").Select
   Application.CutCopyMode = False
   Selection.Copy
   Range("BB9").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=[color=darkblue]False[/color], Transpose:=[color=darkblue]False[/color]
   Application.CutCopyMode = [color=darkblue]False[/color]
   ActiveSheet.PageSetup.PrintArea = "$AW$7:$BO$39"
       [color=darkblue]With[/color] ActiveSheet.PageSetup
       .LeftMargin = Application.InchesToPoints(0.25)
       .RightMargin = Application.InchesToPoints(0)
       .TopMargin = Application.InchesToPoints(0.25)
       .BottomMargin = Application.InchesToPoints(0.25)
       .HeaderMargin = Application.InchesToPoints(0)
       .FooterMargin = Application.InchesToPoints(0)
       .PrintHeadings = False
       .PrintGridlines = False
       .CenterHorizontally = [color=darkblue]True[/color]
       .CenterVertically = [color=darkblue]True[/color]
       .Orientation = xlLandscape
       .Zoom = 98
   [color=darkblue]End[/color] [color=darkblue]With[/color]
    
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=[color=darkblue]True[/color]
   
   ActiveSheet_Protect                [color=green]'By Password[/color]
   ProtectWorkBook                    'By Password
   Sheets("Daily Clinical Review").Select
   Application.ScreenUpdating = [color=darkblue]True[/color]
   Range("A6").Select
   

[color=darkblue]End[/color] [color=darkblue]Sub[/color][/face] [/code]


If you're willing to try to help it would be much appreciated.

ANSWER: Ken, Don't know what you did, but you code is all screwed up with some type of HTML formatting I guess. I won't try to dig out the key information from that. I will just give you a hypothetical example

List of names in Sheet1, cells A1:A50

form on Sheet2 where each name will be placed in B9. Assume sheet2 is protected and hidden when the macro is run and that the password for sheet2 is "ABC"

Sheet2 has already been set up so the Print Area prints the correct portion of the worksheet and all print settings are already in place.

Sub PrintNames() Dim sh1 as worksheet, sh2 as worksheet Dim r1 as Range, cell as Range set sh1 = Worksheets("Sheet1") set sh2 = Worksheets("Sheet2") Sh2.Unprotect Password:="ABC" sh2.Visible = xlSheetVisible set r1 = sh1.Range("A1:A50") for each cell in r1

 sh2.Range("B9").Value = cell
 sh2.printout

Next sh2.Protect Password:="ABC" sh2.Visible = xlSheetHidden End Sub

When you record a macro, you are selecting cells and selecting sheets and so forth. All that action gets recorded, but none of it is necessary. As you see in the code above, I don't have any select or activate statements and usually they are not needed.

Hopefully you can adapt that code to fit your needs.



---------- FOLLOW-UP ----------

QUESTION: I would like to apologize for the confusing code display. I was in a hurry and thought I was someplace else.

I examined your code last evening at work and by itself, it would worked just fine.

I have a few questions though.

Question 1 = Does the form that I'm printing need to be on a separate page? This is not a major issue, just I've seen it set up in demonstration code the same way. So I was just wondering if there is a particular reason.

Question 2 = Since it was brought to my attention at work that there are two locations on the form that has to be filled. That would mean instead of one column like my original question. There would be two columns, and the information would be placed into two locations on the form.

I played around with the code that you gave me, but since I don't understand everything to do with it. This was as far as I got.

I hope this is more readable.

Sub PrintNames()

   Dim sh1 As Worksheet, sh2 As Worksheet
   Dim r1 As Range, r2 As Range, cell As Range
   Set sh1 = Worksheets("Sheet1")
   Set sh2 = Worksheets("Sheet2")
   sh2.Unprotect Password:="ABC"
   sh2.Visible = xlSheetVisible
   
   Set r1 = sh1.Range("A1:A50")
   For Each cell In r1
   sh2.Range("B9").Value = cell
   
   Next
   
   Set r2 = sh1.Range("B1:B50")
   For Each cell In r2
   sh2.Range("E9").Value = cell
   
   sh2.PrintOut

Next

   sh2.Protect Password:="ABC"
   sh2.Visible = xlSheetHidden

End Sub

Of course this would only print the second range.

So I know I'm doing something (probably major) wrong.

I would like to thank you for your valuable time in your assistance with my questions.

ANSWER: Ken,

No, the list and the "form" can be on the same worksheet. You would just need to set up your print area so only the form was printed. Another way would be to specify the range to printout

sh1.Range("B9:J20").Printout

as an example --- You would only want one loop and you would update each value in the same iteration of the loop as shown below.

Sub PrintNames()

  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim r1 As Range, r2 As Range, cell As Range
  Set sh1 = Worksheets("Sheet1")
  Set sh2 = Worksheets("Sheet2")
  sh2.Unprotect Password:="ABC"
  sh2.Visible = xlSheetVisible
  
  Set r1 = sh1.Range("A1:A50")
  For Each cell In r1
    sh2.Range("B9").Value = cell
    Sh2.Range("E9").Value = cell.offset(0,1)
  
    sh2.PrintOut
  Next
  sh2.Protect Password:="ABC"
  sh2.Visible = xlSheetHidden

End Sub



---------- FOLLOW-UP ----------

QUESTION: Hopefully this is the last question in this series.

I have a test workbook going and this is where I'm at with the print code.

Sub PrintNames()

 Dim sh1 As Worksheet
 Dim r1 As Range, cell As Range
 Set sh1 = Worksheets("Sheet1")
 
   Set r1 = sh1.Range("A1:A16")    
   For Each cell In r1
   sh1.Range("B9").Value = cell
   sh1.Range("E9").Value = cell.Offset(0, 1)
 
   sh1.Range("B9:J20").PrintOut
 Next

End Sub

Ok, For sake of discussion, I have a range ("A1:A16"). The above formula prints everything in this range Plus range ("B1:B16"). The problem is,that it prints even A16, which is blank.

What I now need is (I mentioned this slightly in the beginning) a method of counting how many fields, from A1 to A16 actually have something in them. And it has to ignore the formulas in those fields. These formulas have already removed blank information from other locations. Thus creating a solid group of names, starting at the top and moving down. For this example, there could be two names at the top or all 16.

In Excel, I can do a (If) in C1 to C16 to show a 1 in each field that has a corresponding name. Then do a sum in C17 to get a total.

What I don't know is how to get that number in here (Set r1 = sh1.Range("A1:A16") ) so that we are only printing forms that actually have names on them.

I imagine there is a way to do the sum in VBA. , but of course I have no idea.

Simply put, the formula needs to be able to detect the number of names in the range and stop printing when it reaches that number.

As I've said before,

Answer

using a number in the current context would not work unless you wanted to add a loop counter

For i = 1 to #of cells to print


Next

is what your are describing. But why not just determine if each cell has a value or not:

Sub PrintNames()

Dim sh1 As Worksheet
Dim r1 As Range, cell As Range
Set sh1 = Worksheets("Sheet1")

  Set r1 = sh1.Range("A1:A16")    
  For Each cell In r1
    if len(trim(cell.text)) > 0 then
       sh1.Range("B9").Value = cell
       sh1.Range("E9").Value = cell.Offset(0, 1)
    end if

  sh1.Range("B9:J20").PrintOut
Next

End Sub

if you really want to use the count of the value 1 in c1:C16

Sub PrintNames()

Dim sh1 As Worksheet
Dim r1 As Range, cell As Range
Dim cnt as Long, cnt1 as Long
Set sh1 = Worksheets("Sheet1")
cnt = Application.countif(sh1.Range("C1:C16"),1)
' or
' cnt = Application.Sum(sh1.Range("C1:C16"))
  Set r1 = sh1.Range("A1:A16") 
  cnt1 = 0   
  For Each cell In r1
    cnt1 = cnt1 + 1
    if cnt1 > cnt then exit for
    sh1.Range("B9").Value = cell
    sh1.Range("E9").Value = cell.Offset(0, 1)

    sh1.Range("B9:J20").PrintOut
Next

End Sub

Hopefully one of those will work for you.




Question

I have this page in my workbook, there is a column of names. On the same page is a form.  One name at a time needs to be copied  to a field in the form so that the form can print and the next name copied and the form prints.


By using the macro recorder, I can create a macro that will copy the first name in the column paste special in the form, highlight the area of the form I want to print and print.

What I'm looking for is some code that will pick the next name in the column and copy it to the Same place on the form Until the list of names has been exhausted. The number of names in column can very day today.

It can't see the formula that will place the name there.

I can't say that I have a good handle on VBA for applications, but I'm working on it.

If you're willing to try to help it would be much appreciated.

Answer

Advertisement

©2021 eLuminary LLC. All rights reserved.