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.

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

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.)

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

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

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

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I still don't understand.

This code:

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

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?



Damian wrote:

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" wrote:

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

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

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

I still don't understand.

This code:

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

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?



Damian wrote:

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" wrote:

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

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

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

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





Damian wrote:

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" wrote:

I still don't understand.

This code:

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

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?



Damian wrote:

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" wrote:

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

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

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

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





Damian wrote:

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" wrote:

I still don't understand.

This code:

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

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?



Damian wrote:

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" wrote:

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

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

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

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.

Damian wrote:

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" wrote:

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





Damian wrote:

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" wrote:

I still don't understand.

This code:

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

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?



Damian wrote:

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" wrote:

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

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

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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.

Damian wrote:

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" wrote:

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





Damian wrote:

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" wrote:

I still don't understand.

This code:

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

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?



Damian wrote:

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" wrote:

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

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

Damian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Damian" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson