Need to input value then repeat a process until value exhausted

**BigMcBen** · 02-09-2013, 04:51 PM

I have a worksheet that is constantly updated with data. I need to take this new data and paste it into another worksheet. I have managed to sort that but what I need to do is for there to be a prompt asking for the start row, end row and date, let's say 100, 109, Monday 23/2/13

I then need the date pasted into sheet 1 cell h1:n3, font size 28. This does not change.

Then I need row 100 from sheet 2 selected. The following would be executed.

Please Login or Register  to view this content.

I then need this to print.

Then I need the next row in sheet 1 to be selected (row 101) and for the same process to repeat and so on until the end row has printed (row 109).

Any help greatly appreciated.

**HaHoBe** · 02-09-2013, 05:22 PM

Hi, BigMcBen,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Ciao,
Holger

**AlphaFrog** · 02-09-2013, 06:18 PM

Try this.

Please Login or Register  to view this content.

**BigMcBen** · 02-09-2013, 07:30 PM

Wow Alphafrog, that is amazing. The only slight glitch is that when the date is inputted, they sometimes specify the day as well, so it could be Monday 11/2/13. If assuming that d As Date makes the program look only for a date so when I add a day it come up as an error. How would I fix this?

**AlphaFrog** · 02-09-2013, 07:58 PM

How would I fix this?

The easiest fix is don't do that.
Just change the Input box prompt to state the proper date entry format.

Please Login or Register  to view this content.

**BigMcBen** · 02-09-2013, 09:35 PM

Ok, I've done as you suggested and there is already today's date offered in the box, but it is american format m/d/yy. Is it possible to make it either uk format or not appear at all?

**AlphaFrog** · 02-10-2013, 04:50 AM

Remove the date:

Please Login or Register  to view this content.

Date as UK format:

Please Login or Register  to view this content.

You're welcome.

**BigMcBen** · 02-10-2013, 10:37 AM

I'm trying to understand what you've written. One glitch that came up was that if you selected say rows 2 and 4, you would print out three pages but what you'd written subtracted 4-2 which gave 2 so I added + 1 to the line and it works, so I'm increasing my knowledge at the same time. I don't think many people actually appreciate just how powerful a tool Excel is and the amazing things you can automate and achieve. Another couple of points if I may. If I wanted to avoid any doubt as to the worksheet to start from, instead of Set ws = ActiveSheet, would I write Set ws = Sheets ("Scotland"), which is the name of the worksheet? Also the system I'll be using it on may have more than one printer. Can I modify the .Printout Copies:=1 to specify the printer address and if so, how?

Again thanks for all of your help and patience.

**AlphaFrog** · 02-10-2013, 02:02 PM

Originally Posted by BigMcBen

would I write Set ws = Sheets ("Scotland"), which is the name of the worksheet? Also the system I'll be using it on may have more than one printer. Can I modify the .Printout Copies:=1 to specify the printer address and if so, how?

Good catch on the rows count.

Yes on the set ws..., but you'll never really know until you try it.

Set the path and printer name to suit.
ActiveSheet.PrintOut Copies:=1, ActivePrinter:="\\print_server\printer_name"

Tips for getting VBA code help:

In the VBA editor, highlight a key word (e.g. Printout) and press the F1 key to get the description, syntax, and arguments of the command (and sometimes example code).
Record a macro to get example code
Do a web search for something like Excel VBA select printer
Or do a Google search for this specific forum like this; select printer site:http:// www.Excelforum.com .