VBA code to copy and past (with all formatting) last row of data from specified column to next row

[craignigel]

Hello

I am from Leeds in UK. I am trying to learn how to write and run simple VBA code for Excel 2007. I have found out how to add the VBA macro to a new module and how to run it or run a line at a time.

I have constructed a simple table for house accounts. It set out in the following way shown below this. Each column with have an entry and is self explanatory. Each credit or debit will be on a separate line. I want to be able to copy with formatting the last line down to the next (blank) line but only columns D to V. Columns A,B & C should be blank but with formatting because new data will be entered manually. Then the budget column will be amended. The last column contains a sum formula for that particular line.

I have found the following VBA code at "Excel Campus" and tried to amend it but it does not quite do what I need.

Sub Copy_Paste_Below_Last_Cell()
'USE THIS Find the last used row in a sheet and copy and paste data below it.
Dim lRow As Long
ThisWorkbook.Activate
'1. Find last used row in destination sheet
lRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row

'Offset 1 row below last used row
lRow = lRow + 1
'2. Copy data
Worksheets("Sheet1").Range("D5:V5").Copy

'3. Paste data
Worksheets("Sheet1").Range("D" & lRow).PasteSpecial

'Clear copy mode (marching ants around copied range)
Application.CutCopyMode = False
End Sub

I would really appreciate any help that you may have time to give me or please send me in the right direction, may be on the web.

Many thanks in advance

Nigel




[TABLE="width: 1042"]
<tbody>[TR]
[TD="class: xl65, width: 73, bgcolor: #D7E4BC"]DATE
[/TD]
[TD="class: xl66, width: 125, bgcolor: #D7E4BC"]CREDIT/DEBIT
[/TD]
[TD="class: xl67, width: 62, bgcolor: #D7E4BC"]£
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Sky
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Water
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Eenergy
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]ComChg
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]CarLoan
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Carbins
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Carseta
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Rent
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Mobile
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]InsHCT
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]DFSTVL
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Food
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Fuel
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Rich
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Over
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]Over1
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]TesC1
[/TD]
[TD="class: xl67, width: 59, bgcolor: #D7E4BC"]TSBStev
[/TD]
[TD="class: xl68, width: 72, bgcolor: #D7E4BC"]TOT BAL

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub Ins()

   With Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)
      .Resize(2, 22).FillDown
      .Offset(1).Resize(, 3).ClearContents
   End With
End Sub

 

[craignigel]

Hello Fluff
Thank you for your welcome to the forum and your very fast reply. It certainly copies all row 5 (the fist line of actual data) to row 6 and then clears the column A,B&C. So now you have two lines of data...that's perfect. Thanks!! It then runs again(copies to row 7 as soon as new data has been entered into A,B,C. It's perfect!! You make is so straight forward.

Many thanks
Nigel

 

[Fluff]

Glad to help & thanks for the feedback

 

[craignigel]

Hello again

Perhaps you might explain one point please regarding the VBA code you posted for me. It appears not to run if used in a table (quick table format). Any ideas why?? An earlier version of my spread sheet was formatted in this way, just for speed really. Many thanks again.

Nigel

 

[Fluff]

Just tried it with a simple table & it works for me. That said, I very rarely use tables, so don't know much about them.

 

[craignigel]

Thanks and I'll take a closer look. Much obliged.