Why separate worksheets when using excel?

Vba printing in Excel 2003

• 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, 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:

  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.