Automatically copy entire row from one column to another when new data is added to a worksheet

SouthernGent0327

New Member
Joined
Jan 30, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I have a master spreadsheet which contains a data log. The data log contains complex formulas, so I would like an entire row to copy to a different column as just values (no formulas) automatically when new data is added to the sheet. I started out creating a module with the following code that will only copy the first row repeatedly.

VBA Code:
Dim sh As Worksheet
Dim lRow1 As Long
Dim lRow2 As Long
Dim i As Long

Set sh = ActiveSheet

lRow1 = sh.Range("AE" & Rows.Count).End(xlUp).Row
lRow2 = sh.Range("AM" & Rows.Count).End(xlUp).Offset(1, 0).Row

With sh.Range("AE13" & ":AJ" & lRow1)
sh.Cells(lRow2, "AM").Resize(1, .Columns.Count).Value = .Value
lRow2 = lRow2 + 1
End With
End Sub

I would really like this to happen automatically every time new data is added to the sheet. Please help and any information is greatly appreciated.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,833
Office Version
  1. 2013
Platform
  1. Windows
contains complex formulas, so I would like an entire row to copy to a different column as just values
IMHO that isn't possible since an entire row can't be shifted (neither to the left nor to the right). I'm sure your intention was to say something else, but we can't guess what that would be exactly.
It would be helpful if you posted some sample data, both from the starting point and the desired result.
 

Forum statistics

Threads
1,141,402
Messages
5,706,245
Members
421,433
Latest member
yash0468

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
Top