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

A

art27

New Member
Joined
Jul 25, 2014
Messages
27
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
I am assuming that we can tell that row 17 is "filled" by C17 being non-blank. If so I would use a while loop thus:

Code: 
Sub Save_Trade()
    
    Range("C12:U12").Select
    Selection.Copy
    
    pasteRow = 17
    While Range("C" & pasteRow) <> ""
        pasteRow = pasteRow + 1
    Wend
    
    Range("C" & pasteRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
End Sub
 
Upvote 0
As per the previous poster, if you want to paste to the next available row

Code: 
Sub Copy_There()

    Range("C12:U12").Copy Destination:=Cells(Range("C" & Rows.Count).End(Direction:=xlUp).Row + 1, 3)
    
End Sub
 
Upvote 0
I have come across a problem and I need some more help please.

I have decided that I don't want to copy Row 12 from C to U. I instead want to copy Row 12 from C to O and from Q to U and by doing this Im not copying anything in column P

Then I want to paste that data in row 17 and so on.

I cant see how I should change the formula.

Sub Save_Trade()

Range("C12:U12").Select
Selection.Copy

pasteRow = 17
While Range("C" & pasteRow) <> ""
pasteRow = pasteRow + 1
Wend

Range("C" & pasteRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub




Thanks again

Alan
 
Upvote 0
Assumes there is data in C16 & Q16. If not you can put a header in each and this should do what you want.


Code: 
Option Explicit

Sub SaveTrade()

 '
 Range("C12:O12").Copy Range("C" & Rows.Count).End(xlUp)(2)
 Range("Q12:U12").Copy Range("Q" & Rows.Count).End(xlUp)(2)
 End Sub
 
 End Sub

Regards,
Howard
 
Last edited:
Upvote 0
art27 said:
I have come across a problem and I need some more help please.

I have decided that I don't want to copy Row 12 from C to U. I instead want to copy Row 12 from C to O and from Q to U and by doing this Im not copying anything in column P

Then I want to paste that data in row 17 and so on.

I cant see how I should change the formula.

Sub Save_Trade()

Range("C12:U12").Select
Selection.Copy

pasteRow = 17
While Range("C" & pasteRow) <> ""
pasteRow = pasteRow + 1
Wend

Range("C" & pasteRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub




Thanks again

Alan
Click to expand...

** Also is there a way to just copy the cell contents without the cell colors and outline?
 
Upvote 0
Try this.


Code: 
Sub SaveTrade()
 '
 ' SaveTrade Macro
 '
 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

 End Sub

Regards,
Howard
 
Upvote 0
L. Howard said:
Try this.


Code: 
Sub SaveTrade()
 '
 ' SaveTrade Macro
 '
 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

 End Sub



Howard
Click to expand...

Thanks Howard.

The macro works just fine but when it completes I'm left with a "selected" area at Q12 to U12. How do I automatically make that area deselect when the macro completes? Hope that makes sense.

Regards,


Alan
 
Upvote 0
Try

Code: 
 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
No cells were found - trying to copy a hidden row range
Replies
3
Views
35
Micron
M
S
  • Solved
Copy row 2 and paste it after last row of another worksheet
Replies
9
Views
852
rollis13
rollis13
L
  • Question
Copy data from the previous upper line and paste to current row
Replies
0
Views
150
Linda Lo
L
itsgrady
  • Question
VBA - Copy and Past to last row
Replies
2
Views
657
itsgrady
itsgrady
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
1
Views
195
bobaol
B

Forum statistics

Threads
1,214,652
Messages
6,120,747
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top