Copy and paste multiple times depending on number specified AND add number to end of text entry (For science research!!)

[PMSK8]

Hi,

I work in a research lab and we need your help!! We've just purchased a slide printer (it works just like a normal thermal printer to put text onto the slide), but it doesn't come with any software and we currently have to print each slide one at a time manually!!! However, it is possible to connect this slide printer to a PC and by using excels "page setup" to make each page exactly the same size as the slides printable area, it's possible to print out a run of slides. Unfortunately, I need some help coding this

I have a first worksheet in excel where we will put the information that we want on the slide. We also need to add a number to each of the slides so we know the sequence that the tissue comes off the machine in, but that wont always start at 1 so we need to be able to define a "slide numbering start at", "slide numbering stop at" so we can work out the number of slides needed. This number then needs to be added to the slide output that will be printed.

In the PRINT Worksheet I've used the =FORM!B3 in the PRINT!A1 etc to get the information from the FORM cells into the PRINT cells. The number in PRINT!B4 is from the "slide start at number" FORM!B12

But what I can't work out is how to copy these 4 rows (and 2 columns) of information to form a sequence defined by the number of slides needed. I want to end up with the following (which I've made manually to explain what I need).

(Ideally I wouldn't want the number in a separate column and it would be amazing if that was added to text in A eg H&E became H&E 1, H&E 2 etc, but happy to go with either).

In vba I managed to use the following to copy and paste manually added text into cells A1:B4, but it didn't work when I used the =FORM!B3 in cell PRINT!A1 etc as the formula was different for each of the pasted cells rather than using the same (original) one in A1 each time.


Sub CopyMulti()

Range("PRINT!A1:B4").Copy Range("PRINT!A5").Resize(4 * Range("FORM!B18"))

End Sub

which resulted in the following output.

I apologise for the longwinded explanation and I'm guessing this is really simple, but I'm just not getting anywhere on my own. You'd be making a group of scientists very happy if you could help!!

 

[Micron]

I'm a novice at Excel vba but not at Access vba. This should not be too hard but if you need to keep these constructs as historical records, then IMO Excel is not the right tool for the job.

Having said that, I think anyone would ask at least this question - where are the start/end values going to come from? The FORM sheet? A userform? Something else?
Sorry if I missed that info.

You might get more/faster help if you copied ranges and pasted them in a post instead of pics. No one can do anything with pictures of data except to manually type it all out. Pasted ranges can be copied from a post. Or use XL2BB from the posting toolbar.

EDIT - I see that your start/stop values are in the sheet. Could work, but will fail if a value is missing or is in the wrong cell. A userform would be better if you can manage creating one. That way, a button can launch the code and values can only be in 2 places. Also, can do validation much easier, such as start is not less than stop, large differences can raise warnings (e.g. someone might mis-key in large numers), etc.

 

[PMSK8]

Hi,
Thanks for that. The plan long term is to have all of this in a database where each user can log into their account and have a long term record of everything they prepare. However, at the moment it’s a completely manual process. (At moment the only other option is a marker pen so an excel spread sheet is a big step forward!!)

The FORM page is where the user would enter all of the data that they want to print out, including the start and stop numbers. The reason this doesn’t always start at 0 is that a user might print out slides 1-10 one day and then the next day want to print out more but starting at 11. ie. Everything is manually entered.

Hope that makes sense,
Thanks, Peter

 

[Micron]

I does.
Is there a good reason why 7 rows of info are spread over 18? I would think that just makes it more difficult. BTW, if start is 12 and end is 16, isn't that 5 slides, not 4?

 

[PMSK8]

Hi,

No reason for the FORM info to be spread over so many lines. I was just trying to space all of the input fields out, but no problem if they are all grouped up with no gaps.

You’re absolutely right that from 12-16 is 5 slides. A basic error on my part
Thanks, Peter