Here's what I got
How do I make it use the input number to copy and paste it that many times
Please Login or Register to view this content.
also would like the input box to open when workbook starts, or at the very least with hotkeys.
I would like the Info from range "A7:H20" to be pasted below H20 with H21 being blank and this to be repeated based on the number input into the inputbox
Thanks!
Last edited by PNick; 04-11-2017 at 04:12 PM.
Hi,
Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
In addition when you do edit your original I suggest you upload an example showing a before and after. It's not clear where the copied ranges should be pasted and whether if they're formulae you want to retain the formulae or values only.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
No help? Or is my question still unclear? Would really prefer not to attach workbook, as it contains confidential information, may have to just make an example if needed.
In brief ...
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
You almost did it for me! Thanks!
It creates one too many copies.
And how do I get it to prompt you upon opening the workbook
You saidIs it not doing that?How do I make it use the input number to copy and paste it that many times
Q2: call the macro in the workbook open Event handler.
You are correct, my question never asked what I wanted it to. The cells its copying counts as 1, then if we input the number 3 I want it to paste 2 more copies.
Also I don't know where or how to "call the macro in the workbook open Event handler".
Please Login or Register to view this content.
Please Login or Register to view this content.
You did it! Thanks! You never quite spelled it out for me, but I somehow fumbled through.
Now how would I paste 3 other things underneath of the amount of copied cells previously determined by the inputbox
The 3 other things are on their own sheets, titled "ADA", "OSBL NG", "T&D ROW".
the cells to be copied are as follows:
sheet ADA cells A5:H20
sheet OSBL NG cells A5:H10
Sheet T&D ROW cells A5:H11
I would also like to keep one blank row between these newly pasted cells, and all formatting of the new cells to be pasted.
If you could pull that off that is all I will ask. My worksheet will be complete, I will be a hero at the office, and you will be my hero.
Thanks for everything!
UPDATE:
I compiled everything else I want pasted under the input box information onto a new sheet "sheet1" and tried this code
It never worked. Can you fix this for me.. probably wayyy off, but it was my attemptPlease Login or Register to view this content.
You've kind of moved on from what was a relatively simple copy and paste to something a little more complex.
It's essentially the same principle locate the last row and copy something and post it two rows further down. Repeat for each of the three sheets.
Sadly, my cape is in the wash... and you will be my hero.
After writing my first post I realized that I could just make one sheet with everything on it that needed to be added to the end.
All I now need it to do is to copy contents from "sheet 1" cell "A7:H20" and paste it into sheet "INSP" underneath the last cell (with a blank row between)
But if you are admitting defeat, I still greatly appreciate the effort and you have been very helpful in getting me this far!
Thanks
Please post a sample workbook as suggested in Post #2. This needs to show the starting point, that is, what is in Sheet1 and Sheet: INSP at the start (when the workbook opens).
It's not a case of admitting defeat ... it's easy enough to do what I think you are asking for, I just don't know where you are starting from and where you want to end up. Bear in mind that you originally asked for cells A7:H20 to be copied and pasted the number of times indicated in the InputBox; then you said that the original cells counted as one of the copies. Then you said copy those cells and add other cells from three other worksheets. Now it's all in one worksheet so, presumably, we're back to the original number of repetitions ... but who knows?
Given that you wanted A7:H20 copying in your original request, it seems odd that you still want A7:H20 copying even though there are, in theory, more cells now.
I'm not really interested in producing code while you design "on the fly".
In essence, all you need to do is adjust the source and target worksheets in the Copy and Paste.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks