PDA

View Full Version : VBA help: Copy, offset, add cells, repeat, all on one button?



JLSillitoe
07-23-2014, 08:21 AM
Hello everyone,
Okay so I'm rather new to excel and slowly getting the hang of it. However I am LOVING the very little that I know about macros! I really want to learn more but I've been a little stuck for the last 2 days trying to make a macro that will be activated by a button. So here's my dilema:

For my tour operation I have a chart set up (range B6:R17) with formulas to calculate a final selling rate for excursions based on different values put in by a user. I want to be able to press a button above my chart that will, when pressed, make a copy of the same exact chart below the original (to a new range of B20:R31) The chart is found inside of a larger work "square" where other date is represented concerning the particular excursion, including the name of the excursion, ect..

So here's the tough part, the spread sheet is full of the same repeated work "square" being used for A LOT of other excursions and their data.

I would like, that when pressed, this button could also run a macro that would somehow create the space needed to fit the newly copied chartunderneath the original work "square" without deleting the data already entered in the other work "squares" below. Maybe by creating more cells that would "shift down" the other work "squares"?

There's also one more thing.. Next to this button is an open cell (G4) where the user can enter a number representing the number of passengers that will be going on the excursion (important when making the calculation). The data in (G4) is copied and pasted over the data originally entered in the fistchart (E8) when the button is pressed and a copy is made.

The idea of having this button is to create a copy of the same chart to have a "side-by-side" (one below the other) comparison between the same excursion but with different prices based on the different number of passengers attending. This button would also have to be able to, when clicked a second time, copy again the same chart, but this time paste under the previously copied chart [new location of second click (B32:R44)] instead of directly under the work "square" as what would have happened the first time the button was pressed. I would like it to be able, in theory, to make as many copies as the number of times a user presses the button. haha.

I hope this makes sense and isn't too long.. I'm pretty sure it's possible I just can't seem to make the pieces fit together and some parts I have no idea what to do.

Anyway, thanks for reading and any tips would be greatly appreciated. I hope all the information is present if anyone wanted to take a stab at it, let me know. Thank you again!

westconn1
07-24-2014, 02:52 AM
i think you would need to post a sample workbook, with before and after sheets, so we can get a better picture of the desired result and what other data is on the sheet besides the copies you want

JLSillitoe
07-25-2014, 07:39 AM
I tried to simulate manually and illustrate what would happen if this could actually work. Here are the results:
Picture (1) 12026Picture (2) 12027Picture (3) 12028Picture(4) 12029 Picture (5) 12030
I hope this illustrates and explains a little bit better what I tried to explain above.

JLSillitoe
07-25-2014, 07:52 AM
Here is the original spreadsheet without any alterations and is how, currently, I would like them all to look and function. I still have to de-bug the buttons for calculations, some aren't relative and don't work when they are copied.. so that will have to change. The other file labeled "(example)" is how the spreadsheet would look after a user pressed the button with the desired macro as illustrated above in the pictures. This file is merely for looks and does not really work, I just copied and pasted everything where it would be. Here is the link to open the items:

JLSillitoe
07-25-2014, 07:52 AM
Post #4

JLSillitoe
07-25-2014, 07:53 AM
Post #5

JLSillitoe
07-25-2014, 07:54 AM
Sorry, :) But here is the link: https://www.dropbox.com/sh/o5xfcs3hxi8i0e4/AACJ1xrBFIp5E4Oln2cPhmDZa