Macro adjusts when adding Columns

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
195
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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
195
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,115
Messages
5,640,199
Members
417,131
Latest member
Seanr19871

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