Copy cells

[devcon]

Can anyone help me in this situation?

Please Login or Register  to view this content.

I need to copy all the data below the cell select (ex J5) and copy to K5 etc.
The cell selected is random ex J5 or J10 etc and I need to copy all the below cells and paste to K.

(See attached an example).

Thank you in advance.

[Steffen Thomsen]

Hi,

Is it allways numbers in cell j thats being copied to cell k?

Then you can use this macro.

Please Login or Register  to view this content.

If it's just from the activecell that you have selected it needs to copy, then just leave out this part

Please Login or Register  to view this content.

Steffen Thomsen

[devcon]

Dear Steffen Thomsen,
Thank you very much, excellent.
Have a nice day.

[devcon]

Now I like to make the same with formulas by I am unable to solve.
Example as per attached sheet 1st post.

Can you help me please?
Thank you.

[quote]

Sub CalculateTotals()
'
' Calculate TotalCost, TotalSales, UnitPrice
'

'
Range("N1").Select
ActiveCell.FormulaR1C1 = "Increase"
Range("N2").Select
ActiveCell.FormulaR1C1 = "13%"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=+RC[-4]*RC[-1]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=+RC[-5]*RC[-4]"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=ROUND(RC[2]*(1+R2C14),2)"
Range("L3:M3").Select
Do Until IsEmpty(ActiveCell.Offset(0, -10))
Selection.Copy
ActiveCell.Offset(0, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select

Loop

End Sub

[Unquote]

[Steffen Thomsen]

Hi devcon,

Firstly, remember to ad code brackets around your code, it's shown in the menu with "#".

Do you want to calculate the total after looping threw cells in column J and copying?
Then it can be inserted into the same macro.

Try explaining more in depth what you are trying to accomplish.

Steffen Thomsen

#Edit: Your workbook macros are password protected, please upload an example that has no password.

[devcon]

Dear Steffen Thomsen,

Thank you for your reply.
Exactly, I need to calculate the total cost and total sales.
The problem that I have is even the empty cells are calculated that is wrong.
In your previous macro that is not occurred.
I don’t know how to solve this problem. I require your precious help.

Sub UnitPrice()
'
' UnitPrice Macro
' Unit Prices
'
Range("N1").Select
ActiveCell.FormulaR1C1 = "Increase"
Range("N2").Select
ActiveCell.FormulaR1C1 = "15.00%"
Columns("N:N").Select
Selection.EntireColumn.Hidden = True
Range("I3").Select
ActiveCell.FormulaR1C1 = "=ROUND(RC[2]*(1+R2C14),2)"
Do Until IsEmpty(ActiveCell.Offset(0, -8))
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

Loop

End Sub

See attached a new copy of the spreadsheet.

[Steffen Thomsen]

Hi Devcon,

In your workbook this column is calculated with 12 %, in your post it says 15 % just make apropriate changes to this code.

Please Login or Register  to view this content.

Glad i could help.

Steffen Thomsen

*Sorry made a macro for something else in your workbook - Will return with the correct macro

[Steffen Thomsen]

Hi Devcon,

Just a couple of questions.

1 - Why not use a table, i can see that you earlier have assigned a rangename to this part, instead of a range name insert a table around it, a table makes the sum part easier.

Please Login or Register  to view this content.

After this you can easily create a macro that sets the table to your preferences and calculats sums and so on.

It works no matter how many columns and rows you have.

2 - Are you creating a macro to do it all for you after inserting products, product price and number of sales?

I cant see the whole macro as you have not removed the password protection.

Steffen Thomsen.