Printrun Range Issue

[samish]

Hi all,

I was hoping that someone could give me a quick hand with a rngLoopRange issue I'm having. I'm currently using the following in order to print off batches of customer statements:

Sub Printrun()

Dim rngLoopRange As Range

For Each rngLoopRange In Sheets("Input").Range("A5:A9")
With Sheets("All Services Template")
.Range("B4") = rngLoopRange 'Value from column A on Data sheet
.PrintOut
End With
Next rngLoopRange

End Sub

The issue is that no one else I work with seems able to step in to the macro and change the range where required, or to even run it. As such I have created a simple button so they can run it, but I want the range to be something that they can type in to the spreadsheet and the macro picks it up. So in cell G5 of "All Services Template they type A5 and in G6 they type A9, giving the range of statements they want to print off. Does that make sense? If someone could give me a hand it would be really appreciated.

Thanks,

Sam

[AB33]

Have you consider changing event macro where the triggering data are in column "A"? Your code seems to loop through range A and copy the value to Range("B4") of "All Services Template" sheet with not pre condition reason for looping. What is the reason for looping and copying?

[samish]

Hi AB33, thanks for responding. So column A of the input sheet contains unique reference numbers. The macro currently takes a unique ref, places it in B4 of All Services Template, and a series of VLOOKUP's fill in the rest of the template based on that. The template is then printed, and it loops to the next unique ref. So what I would like is for people to be able to enter the range they wish to print within an area of the spreadsheet and press the button so that only that range is looped, without them having to alter the range within the macro itself.

[6StringJazzer]

I suggest you tell user to select the range they want, and then in your code use Selection instead of Sheets("Input").Range("A5:A9"). This is much more user-friendly, less error-prone, and more Excel-like for your users than having them type in a range address. You should also set the button's property TakeFocusOnClick to False. You may need some error checking on Selection to make sure they haven't selected some random cells that will cause problems.

Normally I steer people away from using Selection in code but this is the type of case where it's appropriate.

Also please note:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[samish]

Thanks for that, I will experiment with it and see what happens.

Sorry about the rule breaking; will make sure I familiarise myself with them so it won't happen agian.