Adding Rows or Columns disorders my micro

[Damian]

I have a group of cells that I copy certain information from and paste it to
another cell for user to see.

When the user adds rows or columns this macro stops working, because the
Range changes for those cells.

How can I freeze them so that row adding wont affect them?

Thank You.

 

[Luke M]

Please post at least (preferably all) of your macro so we know what it is
your trying to do, and how we can adjust to fit your needs.

 

[Damian]

Here is part of the code:

With Range("AF51:AF52").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With

SO now if you add extra row the data needed to be copied is in AF52:AF53.
How can I make them Stay the same?

 

[Dave Peterson]

Range("AF51:AF52").Copy _
destination:=Range("I2")

How would the code know what rows to include?

Can you just go to the last used row in column AF?

with activesheet
.range("af51", .cells(.rows.count,"AF1").end(xlup)).copy _
destination:=.range("I2")
end with

(I like to qualify my ranges.)

 

[Damian]

Thank You for this code:
"Range("AF51:AF52").Copy _
destination:=Range("I2")
"
Much better version of mine childlish one.

To Answare Your Questions:
I have a web query that refreshes the info then I have a code to copy and
paste it somewhere so the user can see it.

This is the only information in column AF.

When I inserter your second code it gave me an error at line:
".range("af51", .cells(.rows.count,"AF1").end(xlup)).copy _
destination:=.range("I2")
"
Run-Time error '1004'
Application-defined or object-define error.

How come?

Thank you.

 

[Dave Peterson]

There was a typo (I included an extra 1 in the column (AF1 should have been
AF)):

With ActiveSheet
.Range("af51", .Cells(.Rows.Count, "AF").End(xlUp)).Copy _
Destination:=.Range("I2")
End With

 

[Damian]

That helped. Thank You

BUT
Now when it copies it copies to cell I3, when extra row added. When I add
another it copies to cell I4 and so forth.

Is there a way to fix this?

 

[Dave Peterson]

I don't understand. The code that's posted always pastes into I2.

(But it pastes into as many cells as the copied range.)

That helped. Thank You

BUT
Now when it copies it copies to cell I3, when extra row added. When I add
another it copies to cell I4 and so forth.

Is there a way to fix this?

 

[Damian]

I think it takes that extra row I created and copies it also. so when I
insert one row it will paste into "I2" but there is 1 blank cell so the two
lines of text are now in "I3 & I4"
When I add another row there will be 2 blank rows so the text will be in I4
and I5 and so forth.

everytime I insert a row the code copies that extra row also and is throwing
the text off.

Is there a way of fixing this?

Thank You

 

[Dave Peterson]

I still don't understand.

This code:

copies the range in AF51 to the last used cell in column AF. If you included
empty cells in that range, then you'll get empty cells in the pasted range.

If you inserted a new row at the bottom of the range (below the last used cell
in column AF), then the code won't include it.

Is there a chance that AF51 is empty to start?

I think it takes that extra row I created and copies it also. so when I
insert one row it will paste into "I2" but there is 1 blank cell so the two
lines of text are now in "I3 & I4"
When I add another row there will be 2 blank rows so the text will be in I4
and I5 and so forth.

everytime I insert a row the code copies that extra row also and is throwing
the text off.

Is there a way of fixing this?

Thank You

 

[Damian]

AF51 is not empty to start. But when you insert an extra row in the begining
of worksheet then AF51 now becomes empty.And what I want to copy starts at
AF52. When you insert another row AF51 and AF52 are empty and the text moves
to AF53. If there a way to do like +1 every time a row is inserted? OR just
take the 2 last rows in column AF?

Thank You

 

[Dave Peterson]

Select AF51
Then give that cell a nice name
Insert|Name|Define (xl2003 menus)
I used the name "TopCell" in my testing.

With ActiveSheet
.Range("topcell", .Cells(.Rows.Count, "AF").End(xlUp)).Copy _
Destination:=.Range("I2")
End With

I think this syntax is better documentation:

With ActiveSheet
.Range(.range("topcell"), .Cells(.Rows.Count, "AF").End(xlUp)).Copy _
Destination:=.Range("I2")
End With

====

or if you really only wanted the last two used cells in column AF:


With ActiveSheet
.Cells(.Rows.Count, "AF").End(xlUp).Offset(-1, 0).Resize(2, 1).Copy _
Destination:=.Range("I2")
End With

AF51 is not empty to start. But when you insert an extra row in the begining
of worksheet then AF51 now becomes empty.And what I want to copy starts at
AF52. When you insert another row AF51 and AF52 are empty and the text moves
to AF53. If there a way to do like +1 every time a row is inserted? OR just
take the 2 last rows in column AF?

Thank You

 

[Damian]

YES! Perfect. Love this code:
With ActiveSheet
.Range(.range("topcell"), .Cells(.Rows.Count, "AF").End(xlUp)).Copy _
Destination:=.Range("I2")
End With

I was actually thinking of defining the name and doing it that way when I
was eating my lunch. I was going to ask you if that would work, but you beat
me to it...Thank You so much.

Regards,
Damian

 

[Dave Peterson]

Lots of times, it'll be quicker to try it and see if works, too.

But do yourself a favor when you do this. Either test it against a copy of the
workbook--or save before you run the code. That way you can close without
saving and get things back the way they were before you ran the macro.

YES! Perfect. Love this code:
With ActiveSheet
.Range(.range("topcell"), .Cells(.Rows.Count, "AF").End(xlUp)).Copy _
Destination:=.Range("I2")
End With

I was actually thinking of defining the name and doing it that way when I
was eating my lunch. I was going to ask you if that would work, but you beat
me to it...Thank You so much.

Regards,
Damian

 

[Damian]

Yes I know. I always save before running the code.
Learned the hard way once already.
Thank You.