Macro to copy last row to next row, Formula and format

Hudco

Board Regular
Joined
Jan 4, 2006
Messages
125
Office Version
  1. 365
Good evening,
I have a sheet with data starting in row 7
Each row below will record data but the cell in column A will have a formula.
I am trying to create a macro that will copy the data from the last row (say row 11) to the next row (row 12).
More specifically, I only need to copy the formula in the column A cell and the formats of the cells in column A to the last column with data.

At the moment columns A to I have data but more may be added but only "A" will have a formula.

All answers gratefully received.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I am a bit confused. Are you wanting to copy the data in columns A to I to the next row or just the formula in column A. Your request is not clear on this matter.
 
Upvote 0
Apologies for confusion; The following is what I have been working on since posting:

1. Private Sub CommandButton1_Click()
2. Application.ScreenUpdating = False
3. Dim Lastrow As Long
4. Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
5. Range("A" & Lastrow).Copy
6. Range("A" & Lastrow + 1).PasteSpecial Paste:=xlFormats
7. Application.CutCopyMode = False

8. Dim LC As Long
9. LC = Cells(Rows.Count, "A").End(xlUp)
10. Range("A" & LC).Copy
11. Range("A" & Lastrow + 1).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

So lines 8 to 11 work.
Lines 1 to 7 are meant to find the last row with data, copy the format of the row from column A to the last column with data in that row, and paste the format to the next row. At the moment columns A to M have data but more columns may be added. Column A has the only formula that I want to copy and that part of the macro works. But I want the formats of the row from column A to ?(last column with data) to be copied to the next row which is the part I am having problems with.
 
Last edited:
Upvote 0
Try:
Code:
Private Sub CommandButton1_Click()

    Dim x   As Long
    Dim y   As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        y = .Cells(x, .Columns.Count).End(xlToLeft).Column
        With .Cells(x, 1).Resize(, c)
            .Copy
            With .Offset(1)
                .PasteSpecial xlPasteFormats
                .PasteSpecial xlPasteFormulas
            End With
        End With
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating= True
    End With
    
End Sub
 
Last edited:
Upvote 0
Thanks Jack,

I get a "Run-time Error 1004" at the line

With .Cells(x, 1).Resize(, c)

Any ideas because I am still trying to nut out what your code means - I'm pedantic - I like to know why code works.

Regards,

Clyde
 
Upvote 0
Hi Jack,

I managed to create code to suit/work but yours is a lot tidier so I will sit down over the weekend and get to understand.

This is a great forum not just for solutions but for learning.

Thanks for your help.

Clyde
 
Upvote 0
Typo. try:
Rich (BB code):
Private Sub CommandButton1_Click()

    Dim x   As Long
    Dim y   As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
'Find last row in column A
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
'Find last column for row X
        y = .Cells(x, .Columns.Count).End(xlToLeft).Column
'With range [x1:xy]
        With .Cells(x, 1).Resize(, y)
            .Copy
            With .Offset(1)
                .PasteSpecial xlPasteFormats
                .PasteSpecial xlPasteFormulas
            End With
        End With
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    
End Sub
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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