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

 

[L. Howard]

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

If a cell has a formula in it, it is referring to another cell somewhere for the data to return to the cell in which the formula resides. So, if you think about it you cannot remove the data it is returning and leave the formula. You would need to go to the cells that are referred to by the formula and delete the contents of that cell.

If A1 = 100 and B1 has the formula =A1 then B1 will show 100. Only way to get rid of the 100 is to delete the contents of A1. You cannot "tell" B1 to not return the 100 now because I am ready for new info.

So with those cells you will need to make a decision on how to handle them.

I'm unsure about the #VALUE column of formulas. I'll poke about an may be able to figure something out. And as it is not in the theme of the original title of this thread, you will have a limited number of lookers.

You may want to take that question to a new post for best results.

Howard

 

[ozbod]

Along with what Howard said, I think you are getting yourself (and us) confused.

From my recent reading, and original post, at some point you want to delete a certain number or rows after row 17.

If your original row 12 had formulas, they would have been copied, but they would then be converted to referential changes (ie if row 1 said A12 - B12 then row 17 would say A17-B17)

So deleting rows into the future, (say You only want a maximum number of rows of 1,000) then build that at the end (if total rows > 1,000 then delete all> 1,000)

 

[art27]

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

If a cell has a formula in it, it is referring to another cell somewhere for the data to return to the cell in which the formula resides. So, if you think about it you cannot remove the data it is returning and leave the formula. You would need to go to the cells that are referred to by the formula and delete the contents of that cell.

If A1 = 100 and B1 has the formula =A1 then B1 will show 100. Only way to get rid of the 100 is to delete the contents of A1. You cannot "tell" B1 to not return the 100 now because I am ready for new info.

So with those cells you will need to make a decision on how to handle them.

I'm unsure about the #VALUE column of formulas. I'll poke about an may be able to figure something out. And as it is not in the theme of the original title of this thread, you will have a limited number of lookers.

You may want to take that question to a new post for best results.

Howard

Yes I think you are right. I didn't think that through. There is no need to clear the contents where the formulas are since they will mainly be blank once the other cells are cleared.

The only problem I have left is trying to hide the #VALUE! that is left in some of the cells with formulas once the cells that they are receiving there data from have been cleared. Since its not related I will start another thread.

Thanks and sorry for the trouble

Alan

 

[art27]

I have managed to solve the formula so that #VALUE! no longer appears in any cells and there was no need to try and clear the cells with formulas in them after all.

If I want to alter the original macro so that one of the cells that was copied will be pasted into row 17 like we did before but not in a corresponding column how do I do that?

I have altered the original macro a bit but the main difference is I want to copy cell H12 and paste it in cell P17. When P17 is full the next one is P18 and so on.


Sub SaveTradeEntry()
'
' SaveTradeEntry Macro
'
Range("B12:G12").Copy
Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
Range("H12").Copy
Range("P17").End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Range("B2").Activate ' Or the cell you want to be in

End Sub

 

[L. Howard]

You have this line

Range("P17").End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas

I think you want this line

Range("P" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas

And remember you will need a header or data in P16.

Howard

 

[art27]

You have this line

Range("P17").End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas

I think you want this line

Range("P" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas

And remember you will need a header or data in P16.

Howard

Hi Howard. Thanks it works fine. Can I just ask you how that formula knows to plot the row at row 17?

 

[L. Howard]

Hi Alan,

Sure, what it does is goes to the bottom of column P then looks up the column until it finds an occupied cell. The (2) tells the code come back down 1 row and paste there.
Something to be aware of, if you have other data down the sheet, and there is something in column P, say row 200, that is where the paste will take place. So you may think the code is not working because you were expecting it to paste in row 17 while it is pasting off screen.

Also it will not paste in P17 the first time UNLESS you have something in P16 to act as a header, where upon it will paste correctly.
Now, behind all that there is code that will make it paste in P17 even if there is nothing in P16, and then subsequent pastes below P17, but it is a bit more complicated than just adding a false header.

If you prefer not to have a false header I will see if I can come up with the proper code.

Run the code below with an X in P16 and note the results.

P16 is overwritten, then there is a 1 row offset for the paste in P17, and then there is a 2 row offset from P17 to paste into P19.

Howard

Sub SaveTradeEntry_Offset_Test()
 
 ' SaveTradeEntry Macro
 '
 'Range("B12:G12").Copy
 'Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
 
 '*****  Run these three options below for demo  *******
 
 Range("H12").Copy
 
   'Over writes last occupied cell in column P
 Range("P" & Rows.Count).End(xlUp)(1).PasteSpecial Paste:=xlPasteFormulas
   
   'Offsets 1 row from last occupied cell in column P
 Range("P" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
 
    'Offsets 2 rows from last occupied cell in column P = a blank row between copies
 Range("P" & Rows.Count).End(xlUp)(3).PasteSpecial Paste:=xlPasteFormulas
 
 Application.CutCopyMode = False
 Range("B2").Activate ' Or the cell you want to be in

 End Sub

 

[art27]

Hi Alan,

Sure, what it does is goes to the bottom of column P then looks up the column until it finds an occupied cell. The (2) tells the code come back down 1 row and paste there.
Something to be aware of, if you have other data down the sheet, and there is something in column P, say row 200, that is where the paste will take place. So you may think the code is not working because you were expecting it to paste in row 17 while it is pasting off screen.

Also it will not paste in P17 the first time UNLESS you have something in P16 to act as a header, where upon it will paste correctly.
Now, behind all that there is code that will make it paste in P17 even if there is nothing in P16, and then subsequent pastes below P17, but it is a bit more complicated than just adding a false header.

If you prefer not to have a false header I will see if I can come up with the proper code.

Run the code below with an X in P16 and note the results.

P16 is overwritten, then there is a 1 row offset for the paste in P17, and then there is a 2 row offset from P17 to paste into P19.

Howard

Sub SaveTradeEntry_Offset_Test()
 
 ' SaveTradeEntry Macro
 '
 'Range("B12:G12").Copy
 'Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
 
 '*****  Run these three options below for demo  *******
 
 Range("H12").Copy
 
   'Over writes last occupied cell in column P
 Range("P" & Rows.Count).End(xlUp)(1).PasteSpecial Paste:=xlPasteFormulas
   
   'Offsets 1 row from last occupied cell in column P
 Range("P" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteFormulas
 
    'Offsets 2 rows from last occupied cell in column P = a blank row between copies
 Range("P" & Rows.Count).End(xlUp)(3).PasteSpecial Paste:=xlPasteFormulas
 
 Application.CutCopyMode = False
 Range("B2").Activate ' Or the cell you want to be in

 End Sub

Thanks Howard for explaining that. I now totally understand how it works. I really appreciate the time that you guys put in to help others. Its a great thing to do. Thank you.

 

[L. Howard]

You're welcome.

Regards,
Howard