Vba printing in Excel 2003
Contents
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