Excel Spread sheet

lindseyschlabac · 12-15-2006, 04:50 AM

I have an excel spreadsheet that I want to print out. I want everything to be exactly the same on each print out, except for a name.

I have 25 different names I want to be in 1 cell.

Each different name would be by itself.

I need to print this out once every week.

I don't want to copy and paste it into different sheets becasue I want to be able to change the setup on the sheet and have it affect every sheet except for the name area.

Any idea's on how to accomplish this?

**Glaswegian** · 12-15-2006, 01:42 PM

Hi

Some questions I'm afraid. What is the cell address? How is this cell populated - by hand, by code, the result of a formula or what? I take it you are referring to just one sheet and want to change one cell value 25 times, once each time before printing? Or have I misunderstood? Please tell me as much as you can about your setup and what you require.

lindseyschlabac · 12-16-2006, 04:11 AM

Quote:

Originally Posted by Glaswegian

Hi

Some questions I'm afraid. What is the cell address? How is this cell populated - by hand, by code, the result of a formula or what? I take it you are referring to just one sheet and want to change one cell value 25 times, once each time before printing? Or have I misunderstood? Please tell me as much as you can about your setup and what you require.

The cell address is A3.

I have a list of 25 names I could place them on an excel sheet to be populated or I could put it in the code. The names will not change. What would be best?

You haven't misunderstood. I need 25 of the same sheet except each sheet has a different name in A3.

**Glaswegian** · 12-16-2006, 09:34 AM

Hi Lindsey

This should work nicely - I've used the Before_Print event to change the value of A3 to a name in the list, then print the sheet before moving on to the next name. The code goes in ThisWorkbook Module.

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myRng As Range

Set myRng = Sheets("Sheet1").Range("A3")

Sheets("Sheet1").Range("K1").Activate

Do Until IsEmpty(ActiveCell)
    myRng.Value = ActiveCell.Value
    ActiveSheet.PrintOut copies:=1
    ActiveCell.Offset(1, 0).Activate
Loop
End Sub

Remember to change the range values to suit wherever your list is located (it can be on another sheet if required) - I just used column K for testing purposes. Note the code assumes that there will be a blank cell after the last name in your list - this is vital!

Let me know if this works.

12-15-2006, 04:50 AM	#1 (permalink)
lindseyschlabac Registered User Join Date: Oct 2004 Posts: 203 OS: Win XP	Excel Spread sheet I have an excel spreadsheet that I want to print out. I want everything to be exactly the same on each print out, except for a name. I have 25 different names I want to be in 1 cell. Each different name would be by itself. I need to print this out once every week. I don't want to copy and paste it into different sheets becasue I want to be able to change the setup on the sheet and have it affect every sheet except for the name area. Any idea's on how to accomplish this?

12-15-2006, 01:42 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,512 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Some questions I'm afraid. What is the cell address? How is this cell populated - by hand, by code, the result of a formula or what? I take it you are referring to just one sheet and want to change one cell value 25 times, once each time before printing? Or have I misunderstood? Please tell me as much as you can about your setup and what you require. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

12-16-2006, 09:34 AM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,512 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Lindsey This should work nicely - I've used the Before_Print event to change the value of A3 to a name in the list, then print the sheet before moving on to the next name. The code goes in ThisWorkbook Module. Code: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim myRng As Range Set myRng = Sheets("Sheet1").Range("A3") Sheets("Sheet1").Range("K1").Activate Do Until IsEmpty(ActiveCell) myRng.Value = ActiveCell.Value ActiveSheet.PrintOut copies:=1 ActiveCell.Offset(1, 0).Activate Loop End Sub Remember to change the range values to suit wherever your list is located (it can be on another sheet if required) - I just used column K for testing purposes. Note the code assumes that there will be a blank cell after the last name in your list - this is vital! Let me know if this works. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner