Can macro adjust for adding Columns/rows ??

[StanSz]

I have the following macro, which does what I need it to do, copy data from one column to another.

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


This macro now copies the data from row T31 to row T549 over to E31 to E549. These are all the data rows currently in the Worksheet. 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 etc?? Rows will not be added above row 31, nor will rows be added below what is the current row 549.


This macro now copies data from Column T to Column E.
Is there a way to adapt this macro so that if columns are added or deleated before Column T that the data will still be copied correctly -- that is from what was originally Column T to the column that was originally Column E ?? Again the data to be copied will be between rows 31 and 549

If the Excel board wizards do NOT think that doing this is possible I would appreciate hearing that as well, so that I don't keep asking this same question.


Thanks

StanSz

 

[erik.van.geit]

Hello, StanSz,

you can code like this
(one line instead of 5 )

Range("T31:T549").Copy Range("E31")

to solve your problem, I think you could use named ranges
name T31: FirstCell
name T549: LastCell
name E31: TargetCell
then the code would be

Range("FirstCell:LastCell").Copy Range("TargetCell")

of course don't delete these cells (or the columns with those cells)
does this help ?

kind regards,
Erik

 

[StanSz]

Thanks Erik,

Yes, one line instead of five is helpful, but how does this address the problem of my inserting a new row at row 31, which bumps down all the other rows and thus, the 'Last Cell' would be T550 instead of T549 ??

And what if a column is added before Column D, then how would the macro know that it should copy from Column U (old column T) to Column F (old column E) ??

Thanks

StanSz

 

[izzy]

This only works if you have Nothing in row 30 except for headers above your Data Column and Target Column.

Sub testy()
Range("IV30").Select
Selection.End(xlToLeft).Offset(1).Select
Range(Selection, Selection.Offset(518)).Copy
ActiveCell.Offset(-1).Select
Selection.End(xlToLeft).Offset(1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub




If there is other data or headers in row 30 then you could search row 30 for the headers you are after... but I can't accurately help you with that.

 

[erik.van.geit]

StanSz,
when you wrote

Yes, one line instead of five is helpful, but how does this address the problem of my inserting a new row at row 31, which bumps down all the other rows and thus, the 'Last Cell' would be T550 instead of T549 ??

And what if a column is added before Column D, then how would the macro know that it should copy from Column U (old column T) to Column F (old column E) ??

did you try out my suggestion ?
click in T31
click in namebox
type FirstCell
enter
insert a line or column
where is the range FirstCell now ?

as far as i understood your problem, this will solve it, since the named range is moving around when inserting columns or rows

best regards,
Erik

 

[StanSz]

Erik,

Now I understand. I thought you were directing me to name the ranges within my VBA macro code, and I was going nowhere. You were directing me to name the cells on the Worksheet itself

Yes, FirstCell and LastCell do move around when I insert or delete rows and columns --- wonderful.

Since I'll always be wanting to include my new inserted row at row 31 in the copy, I'll just name T30 as FirstCell, and E30 as TargetCell and then my adaptation of your VBA macro code will be:

Range("FirstCell+1:LastCell").Copy Range("TargetCell+1") and this will copy from T31 down to my Last Cell.

Thanks for the help, and sorry for not immediately understanding your simple and elegant solution.

StanSz

 

[erik.van.geit]

Thanks for the help, and sorry for not immediately understanding your simple and elegant solution.

you're really very welcome
I never think people don't understand "deliberately" the explanations.
named range is "sheetlanguage"
defined name is "VBAlanguage" I think

kind regards,
Erik

 

[StanSz]

Erik,

One more wrinkle:

The one line of code:
Range("FirstCell:LastCell").Copy Range("TargetCell")
works well, but copies over the formulas from the FirstCell:Last Cell Range.

I actually want to copy over just the values (I'm doing this to avoid a circular reference problem.)

I've tried the following code, but I'm getting errors, and don't know enough how to fix it myself:

Range("FirstCell:LastCell").PasteSpecial (xlPasteValues), Range("TargetCell")

Thanks

StanSz

P.S. My "FirstCell+1" thought works as a formula on the ws, but it doesn't work in a macro

 

[erik.van.geit]

you can find the syntax for your PasteSpecial using the macro recorder: then edit to your ranges

or use this syntax, which I like
(assuming Range("FirstCell:LastCell") is only one column

mem = Range("FirstCell:LastCell").Value
Range("TargetCell").Resize(Range("FirstCell:LastCell").Rows.Count, 1) = mem

more "professional"

Dim rng As Range
Set rng = Range("FirstCell:LastCell")
    With rng
    mem = .Value
    Range("TargetCell").Resize(.Rows.Count, .Columns.Count) = mem
    End With

not tested and didn't check for typos, (lack of time yet), but quite sure of it

best regards,
Erik