Macro / VB - copy cell, paste value and multiply by X

[Supersmithy]

Hi,

I'm trying to get some VB to copy cells, paste the values along a row and multiply all the pasted values in that row by the number in a cell that may be increased or decreased by the user. When the macro is run a second time I want it to copy the same cells but to a new row so the previous figures aren't overwritten.

Here's where I've got to so far;

Please Login or Register  to view this content.

This is copying the cells and pasting the values to where I want them. Where and how would I add the bits to say;

1) Multiply each of the pasted values by the value in cell I4
2) When the macro is next run, do it on a newly inserted row so as not to overwrite the figures produced when the macro was previously run


Any input greatly appreciated

[rylo]

Hi

Is this what you are trying to do? You don't nominate the cell that contains the multiplier, so I've made it 'Option Appraisal'!A140

Please Login or Register  to view this content.

rylo

[Supersmithy]

Hi

Is this what you are trying to do? You don't nominate the cell that contains the multiplier, so I've made it 'Option Appraisal'!A140

Please Login or Register  to view this content.

rylo

Hi Rylo,

Thanks for the help, the code works like a charm. For some reason though, it's pasting on to row 18 of sheet "project builder" as opposed to the row 7 that I need it to. I'm assuming it's something to do with this line;

outrow = WorksheetFunction.Max(7, OutSH.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Row)

But I'm guessing the 7 in that line would suggest its right??

[rylo]

Hi

What that line is supposed to do is paste the results in row 7 if there is nothing already in the output area, then on consecutive rows each time the code is run. I took row 7 as the starting output row from your code, and followed your request to

copy the same cells but to a new row so the previous figures aren't overwritten

Check to see what you have in F7:F17. If you can't see anything, then check for a space.

rylo

[Supersmithy]

Got you. What I have is two tables on the "project builder" sheet. Headings across row 6 for the first table and then a second table below it with the headings across row 17.

If I shift my second table down a bit to start from row 20 and I say entries into the top table using the VB will be limited to a maximum of 10 rows i.e. row 7 down to 17 - what amendment would I need to make to the code to get it to continue to work as it is (not overwriting) but just in the range 7 down to 17? I could do with keeping the two tables on the one sheet you see.

Thanks,

Luke

[Supersmithy]

---------------------------------

[rylo]

Hi

Put up an example workbook so we can see your structure and show exactly what would be put where, and why.

rylo

[Supersmithy]

Hi

Put up an example workbook so we can see your structure and show exactly what would be put where, and why.

rylo

Hi Rylo,

Really appreciate your help on this. The attached example workbook is what I'm working with. Basically it's a tool to show the before and after effects of making improvements to houses. So on the option appraisal sheet columns E and K, row 140 downward show the baseline figures and columns F and L, row 140 downward show the improved figures (in my working version they all have formulas behind them looking to other sheets). Cell I4 on this sheet is the multiplier i.e. number of houses in question.

I've attached the code you've provided me with above to the 'proceed to project summary screen' button. Upon clicking this it copies all the baseline figures and pastes them in to the project builder sheet as values that are multiplied by cell I4. It works like a charm except for the fact that at the moment it pastes the baseline figures in to row 35 when I need it to paste in to the 'Before' table, row 7 onwards.

The next step for me was to extend the code to do the same as it is for the 'baseline figures', but for the 'improved to' figures and get those to be pasted in to row 24 of the project builder sheet onwards. So, each time the button's pressed a line is added to the before table and a line is added to the after table with no previous entries being overwritten, allowing the user to see the cumulative totals as they build a project of multiple houses. As indicated above, it's unlikely that the add to project button will ever be pressed more than 10 times.

[rylo]

Hi

Here goes. I've set this up for the example file structure as it stands. When you remove the extra rows etc, you will have to rebase the process for finding the next row.

I notice that you are also sending 2 results to the same output place (column H). E144 and E145 are both going there.

See if this makes sense

Please Login or Register  to view this content.

rylo

[Supersmithy]

Thanks Rylo, it works perfectly.

Well spotted with the double counting to column H as well. I've amended that error + duplicated the procedure and added it to the back end of the macro, calling it DataSH2 and OutSH2 and changing the cell ranges, allowing my two tables to be added to in one hit.