Macro adjusts when adding Columns

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
I have the following macro, which does what I need it to do, copy data from one column to another.

ApplicationCalculation = xlCalculationManual
Application.EnableEvents = False
Application ScreenUpdating = False

Range("T31:T549").Select
Selection.Copy
Range("E31").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Application.EnableEvents = False
Application ScreenUpdating = False
ApplicationCalculation = xlCalculationAutomatic

Three questions:

1) This macro now copies the data from row 31 to row 549 which are all the rows currently in the Worksheet. How can I adapt the macro to allow for new rows being added at row 31, which will push old row 31 down to row 32, and old row 549 down to 550 ?? Said another way, how can I adapt the macro to copy all of the rows, even when new rows are added ??

2) This macro now copies data from Column T to Column E.
Is there a way to adapt this macro so that when columns are added or deleated, that the data will still be copied correctly -- that is from what was originally Column T to the column that was originally Column E ??

3) What do I need to do to this macro to have it activate whenever the worksheet is Calculated ?? (The columns in question have no user entered data in them. All the data in the columns is the result of calculations)

Thanks

StanSz
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
StanSz said:
I have the following macro, which does what I need it to do, copy data from one column to another.

Three questions:

1) This macro now copies the data from row 31 to row 549 which are all the rows currently in the Worksheet. How can I adapt the macro to allow for new rows being added at row 31, which will push old row 31 down to row 32, and old row 549 down to 550 ?? Said another way, how can I adapt the macro to copy all of the rows, even when new rows are added ??

Would there be any rows after the rows you add? What I mean is, would rows ever be inserted after 549 that you would not want to copy?

2) This macro now copies data from Column T to Column E.
Is there a way to adapt this macro so that when columns are added or deleated, that the data will still be copied correctly -- that is from what was originally Column T to the column that was originally Column E ??[/quote]

Which row would have data in it for every column you want to copy? Row 1?

StanSz said:
3) What do I need to do to this macro to have it activate whenever the worksheet is Calculated ?? (The columns in question have no user entered data in them. All the data in the columns is the result of calculations)

Change it to a Worksheet_Calculate() event.
 

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
Rows would be added at Row 31 -- thereby pushing all previous rows (old 31 becomes new 32, etc). Rows would never be inserted below 549 that would need to be copied.

Regarding the columns: Data currently in Column T, and currently in rows 31 to 549 should be copied to Column E, rows 31 to 549. T31:T549 goes to E31: E549

Thanks

StanSz
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,394
Messages
5,837,011
Members
430,465
Latest member
Mackbay

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