Copy cells in row 12 and paste those rows at row 17. If row 17 is full then row 18 and so on

[art27]

Hello

I'm having a lot of trouble trying to write a macro that will copy the cells in row 12 and then paste the data in those cells into row 17. Once row 17 has been filled I want the next lot of data from row 12 to be pasted at row 18 and so on. What I'm doing is I'm enter data into some cells at row 12 and once this is completed I'm clicking the macro button and I want that data to be pasted at the next available line starting at row 17

I had a go at writing a macro but it seems to add a blank line at row 17 and then everything starts from row 18. What am I doing wrong?

Thanks


Alan



Sub SaveTrade()
'
' SaveTrade Macro
'
'

Range("C12:U12").Select
Selection.Copy
Range("C17:U17").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.EntireRow.Insert Shift:=xlDown
End Sub

 

[art27]

Try

 Sub SaveTrade()
 
 Range("C12:O12").Copy
 Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
 
 Range("Q12:U12").Copy
 Range("Q" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
 
 Application.CutCopyMode = False
 Range("C12").Activate  ' Or the cell you want to be in
 
 End Sub

Howard

Great work. Thanks a lot Howard

 

[L. Howard]

You're welcome.

Howard

 

[art27]

I may have created another problem by asking for the macro to just paste the values and not the color or cell outline. The macro no longer pastes the formula that was within the cell and I need it to.

I may have done this or it might be because of the pastespecial part. Not sure.

 

[art27]

I may have created another problem by asking for the macro to just paste the values and not the color or cell outline. The macro no longer pastes the formula that was within the cell and I need it to.

I may have done this or it might be because of the pastespecial part. Not sure.

I may have fixed it. I changed the code to pasteformulas

 

[ozbod]

art,

You don't necessarily want to paste the formulas, but if you want the formats, an adaptation to Howard's reply

Howard: Not meaning to step on your toes

Sub SaveTrade()
 
 Range("C12:O12").Copy
 Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
 Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormats
 
 Range("Q12:U12").Copy
 Range("Q" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
 Range("Q" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormats
 
 Application.CutCopyMode = False
 Range("C12").Activate  ' Or the cell you want to be in
 
End Sub

 

[L. Howard]

Hi ozbod,

No worries 'bout the toes.

I'm thinking the OP wants the formulas but not the formatting. Seems he fixed it using pasteformulas as he said in post #14


@ art27 This does the formulas and not the color.


Howard

Sub SaveTrade()
 
 Range("C12:O12").Copy
 Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
 
 Range("Q12:U12").Copy
 Range("Q" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
 
 Application.CutCopyMode = False
 Range("C12").Activate  ' Or the cell you want to be in
 
 End Sub

art,

You don't necessarily want to paste the formulas, but if you want the formats, an adaptation to Howard's reply

Howard: Not meaning to step on your toes

Sub SaveTrade()
 
 Range("C12:O12").Copy
 Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
 Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormats
 
 Range("Q12:U12").Copy
 Range("Q" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
 Range("Q" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormats
 
 Application.CutCopyMode = False
 Range("C12").Activate  ' Or the cell you want to be in
 
End Sub

 

[art27]

Thanks heaps. Seem to have it sorted. Really appreciated the help today

 

[art27]

Thanks heaps. Seem to have it sorted. Really appreciated the help today

I said that a bit too soon. This is a bit unrelated to the original topic. The macro will now paste the formulas through to the cells below just as I wanted it to. Since then I made up another macro so that I can clear the contents of the worksheet below. The problem is that I want to clear just the data in some of the sells and I want to clear the data and formulas in the others. My macro clears all of the contents so I lose that formulas.

How do I make a macro to do the following?

Clear all of the contents (formulas, data) from cells B17 to G17 and all rows down to 1000 starting at 17
Clear the data only (Not the formulas) from cells H17 to T17 and all rows down to 1000 starting at 17

Thanks

 

[L. Howard]

Hi art27,

So the cells as noted here all have formulas that are returning data to each cell?

Clear the data only (Not the formulas) from cells H17 to T17 and all rows down to 1000 starting at 17

And you want to clear the data that is returned to these cells but leave the formula?

Is that correct?

Howard

 

[art27]

Hi art27,

So the cells as noted here all have formulas that are returning data to each cell?

Clear the data only (Not the formulas) from cells H17 to T17 and all rows down to 1000 starting at 17

And you want to clear the data that is returned to these cells but leave the formula?

Is that correct?

Howard

Yes those cells all have formulas in them and I just want to clear the cells but leave the formulas.


In one column of cells in particular I also need to try and hide the #VALUE! when the other cells are blank. I want the cell that this formula is placed in to be blank unless Cell C17 has text and Cell I17 is blank.

I tried this

=IF(I17="" AND ISTEXT(C17),((O17*F17)-J17)-((D17*F17)+E17),"")


Sorry for all the questions. Finding this a bit hard today.