Adding Rows or Columns disorders my micro
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. |
Adding Rows or Columns disorders my micro
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. |
Adding Rows or Columns disorders my micro
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. |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
Adding Rows or Columns disorders my micro
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 |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com