|
macro code selecting moving range
I am attempting to apply a macro to a file I download and edit each month.
Each month there are more records (rows) in this file. This being so, a portion of the macro is attempting to move around some of the data to format the data consistantly. However, when I have done this it is selecting the same range each time. I would like it to know that when I go to the bottom of the column and then down one row and over 3 rows that this is where I wish it to select my new range to move, no matter where the end.down leads me to. Here is the portion of the code that I'm attempting to change. --- Range("K2").Select Selection.End(xlDown).Select Range("H2343:J2343").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("I2343:K9184") Range("K9184").Select Selection.End(xlUp).Select Range("I6902:J6902").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("J6902:K9184") As you see, the RANGE("...").Select areas are the portions I'm needing to be intelligent enough to know that it's not always that cell that I will be selecting. The cell will always be one row below the row that I end up on when I do the Selection.End(xlDown).Select and then either two or three cells to the left. then they will be moved over one cell. Would this be something like the "RC[-3]" code? I am doing this through the macro recorder and am rather a novice into the actual coding of macros. I hope that this makes sense. Any help would be greatly appreciated!!! |
macro code selecting moving range
Assuming you are copying from H2 to the last row in column K Code: -------------------- Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343") -------------------- KDG;434485 Wrote: I am attempting to apply a macro to a file I download and edit each month. Each month there are more records (rows) in this file. This being so, a portion of the macro is attempting to move around some of the data to format the data consistantly. However, when I have done this it is selecting the same range each time. I would like it to know that when I go to the bottom of the column and then down one row and over 3 rows that this is where I wish it to select my new range to move, no matter where the end.down leads me to. Here is the portion of the code that I'm attempting to change. --- Code: -------------------- Range("K2").Select Selection.End(xlDown).Select Range("H2343:J2343").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("I2343:K9184") Range("K9184").Select Selection.End(xlUp).Select Range("I6902:J6902").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("J6902:K9184") -------------------- As you see, the RANGE("...").Select areas are the portions I'm needing to be intelligent enough to know that it's not always that cell that I will be selecting. The cell will always be one row below the row that I end up on when I do the Selection.End(xlDown).Select and then either two or three cells to the left. then they will be moved over one cell. Would this be something like the "RC[-3]" code? I am doing this through the macro recorder and am rather a novice into the actual coding of macros. I hope that this makes sense. Any help would be greatly appreciated!!! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
OK! Thanks! I think I have this thing almost figured out. Now... the last
thing I need for this thing to do is to delete anything that appears below the last record (by going to the last cell in column A and selecting all the rows below that and deleting them). Here's what the code is erroring out on... ActiveCell.Offset(-1, 1).Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Select Selection.ClearContents I'm assuming that it has something to do with the third line. Any hints??? Thanks you SOOO much for your help and your very speedy reply! It has made my day! "Simon Lloyd" wrote: Assuming you are copying from H2 to the last row in column K Code: -------------------- Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343") -------------------- KDG;434485 Wrote: I am attempting to apply a macro to a file I download and edit each month. Each month there are more records (rows) in this file. This being so, a portion of the macro is attempting to move around some of the data to format the data consistantly. However, when I have done this it is selecting the same range each time. I would like it to know that when I go to the bottom of the column and then down one row and over 3 rows that this is where I wish it to select my new range to move, no matter where the end.down leads me to. Here is the portion of the code that I'm attempting to change. --- Code: -------------------- Range("K2").Select Selection.End(xlDown).Select Range("H2343:J2343").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("I2343:K9184") Range("K9184").Select Selection.End(xlUp).Select Range("I6902:J6902").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("J6902:K9184") -------------------- As you see, the RANGE("...").Select areas are the portions I'm needing to be intelligent enough to know that it's not always that cell that I will be selecting. The cell will always be one row below the row that I end up on when I do the Selection.End(xlDown).Select and then either two or three cells to the left. then they will be moved over one cell. Would this be something like the "RC[-3]" code? I am doing this through the macro recorder and am rather a novice into the actual coding of macros. I hope that this makes sense. Any help would be greatly appreciated!!! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
I have solved my last question... but now I have another. I had the entire
macro up and running on the file that I was working with. But as I stated, I run a new download each month that will contain more records each time. I always open the file into Excel and save it as "LCLI.xls" To test it, I ran a new download to see if it would work. I get an error 9: subscript out of range and the debugger looks like this... the second line is highlighted. What am i not telling it to do???? Cells.Select ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("LCLI.xls").Sort .SetRange Columns("A:K") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "KDG" wrote: OK! Thanks! I think I have this thing almost figured out. Now... the last thing I need for this thing to do is to delete anything that appears below the last record (by going to the last cell in column A and selecting all the rows below that and deleting them). Here's what the code is erroring out on... ActiveCell.Offset(-1, 1).Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Select Selection.ClearContents I'm assuming that it has something to do with the third line. Any hints??? Thanks you SOOO much for your help and your very speedy reply! It has made my day! "Simon Lloyd" wrote: Assuming you are copying from H2 to the last row in column K Code: -------------------- Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343") -------------------- KDG;434485 Wrote: I am attempting to apply a macro to a file I download and edit each month. Each month there are more records (rows) in this file. This being so, a portion of the macro is attempting to move around some of the data to format the data consistantly. However, when I have done this it is selecting the same range each time. I would like it to know that when I go to the bottom of the column and then down one row and over 3 rows that this is where I wish it to select my new range to move, no matter where the end.down leads me to. Here is the portion of the code that I'm attempting to change. --- Code: -------------------- Range("K2").Select Selection.End(xlDown).Select Range("H2343:J2343").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("I2343:K9184") Range("K9184").Select Selection.End(xlUp).Select Range("I6902:J6902").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("J6902:K9184") -------------------- As you see, the RANGE("...").Select areas are the portions I'm needing to be intelligent enough to know that it's not always that cell that I will be selecting. The cell will always be one row below the row that I end up on when I do the Selection.End(xlDown).Select and then either two or three cells to the left. then they will be moved over one cell. Would this be something like the "RC[-3]" code? I am doing this through the macro recorder and am rather a novice into the actual coding of macros. I hope that this makes sense. Any help would be greatly appreciated!!! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
The error 9 is usually because excel cannot find the sheet you are asking it to look for try this and see what sheet names it gives you, it will give you the activeworkbook name and the activesheet name to prove what it is you are working with. Code: -------------------- msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.name Cells.Select ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.name ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("LCLI.xls").Sort .SetRange Columns("A:K") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With -------------------- KDG;435068 Wrote: I have solved my last question... but now I have another. I had the entire macro up and running on the file that I was working with. But as I stated, I run a new download each month that will contain more records each time. I always open the file into Excel and save it as "LCLI.xls" To test it, I ran a new download to see if it would work. I get an error 9: subscript out of range and the debugger looks like this... the second line is highlighted. What am i not telling it to do???? Code: -------------------- Cells.Select ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("LCLI.xls").Sort .SetRange Columns("A:K") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With -------------------- "KDG" wrote: OK! Thanks! I think I have this thing almost figured out. Now... the last thing I need for this thing to do is to delete anything that appears below the last record (by going to the last cell in column A and selecting all the rows below that and deleting them). Here's what the code is erroring out on... ActiveCell.Offset(-1, 1).Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Select Selection.ClearContents I'm assuming that it has something to do with the third line. Any hints??? Thanks you SOOO much for your help and your very speedy reply! It has made my day! "Simon Lloyd" wrote: Assuming you are copying from H2 to the last row in column K Code: -------------------- Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343") -------------------- KDG;434485 Wrote: I am attempting to apply a macro to a file I download and edit each month. Each month there are more records (rows) in this file. This being so, a portion of the macro is attempting to move around some of the data to format the data consistantly. However, when I have done this it is selecting the same range each time. I would like it to know that when I go to the bottom of the column and then down one row and over 3 rows that this is where I wish it to select my new range to move, no matter where the end.down leads me to. Here is the portion of the code that I'm attempting to change. --- Code: -------------------- Range("K2").Select Selection.End(xlDown).Select Range("H2343:J2343").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("I2343:K9184") Range("K9184").Select Selection.End(xlUp).Select Range("I6902:J6902").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("J6902:K9184") -------------------- As you see, the RANGE("...").Select areas are the portions I'm needing to be intelligent enough to know that it's not always that cell that I will be selecting. The cell will always be one row below the row that I end up on when I do the Selection.End(xlDown).Select and then either two or three cells to the left. then they will be moved over one cell. Would this be something like the "RC[-3]" code? I am doing this through the macro recorder and am rather a novice into the actual coding of macros. I hope that this makes sense. Any help would be greatly appreciated!!! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'macro code selecting moving range - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=120622) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
ok... that helped figure out what was going on there... thanks bunches.
Now... two more things. Seems you solve one thing and something else pops up! 1) In order for it to see the correct sheet name I'll have to manually change the sheet name to the name in the code. Right? or have the code recognize the current sheet name no matter what it is? Could this be coded in? I'm assuming so since I know that nothing is impossible, just beyond my experience. 2) Now... when I get to the part of the code that is selecting a range (which is different each time) and moving it to the right one space I get error because it's saying that the selected range and the destination size do not match. I understand what it's saying, but I don't know how to tell it to accept what I'm wanting it to do. here's the code that is the problem... ActiveCell.Offset(1, -3).Range("A1:C1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=ActiveCell.Offset(0, 1).Range("A1:C6842") <--this line and the error code is... Run Time error 1004 The information cannot be pasted because the cut area and the paste area are not the same size and shape. Try one of these: click a single cell and then past or select a rectangle that's the same size and shape, and then paste. ?????????????? Thanks so much for your help. I'm learning TONS!!!! "Simon Lloyd" wrote: The error 9 is usually because excel cannot find the sheet you are asking it to look for try this and see what sheet names it gives you, it will give you the activeworkbook name and the activesheet name to prove what it is you are working with. Code: -------------------- msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.name Cells.Select ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.name ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("LCLI.xls").Sort .SetRange Columns("A:K") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With -------------------- KDG;435068 Wrote: I have solved my last question... but now I have another. I had the entire macro up and running on the file that I was working with. But as I stated, I run a new download each month that will contain more records each time. I always open the file into Excel and save it as "LCLI.xls" To test it, I ran a new download to see if it would work. I get an error 9: subscript out of range and the debugger looks like this... the second line is highlighted. What am i not telling it to do???? Code: -------------------- Cells.Select ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFie lds.Add Key:=Range( _ "H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("LCLI.xls").Sort .SetRange Columns("A:K") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With -------------------- "KDG" wrote: OK! Thanks! I think I have this thing almost figured out. Now... the last thing I need for this thing to do is to delete anything that appears below the last record (by going to the last cell in column A and selecting all the rows below that and deleting them). Here's what the code is erroring out on... ActiveCell.Offset(-1, 1).Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Select Selection.ClearContents I'm assuming that it has something to do with the third line. Any hints??? Thanks you SOOO much for your help and your very speedy reply! It has made my day! "Simon Lloyd" wrote: Assuming you are copying from H2 to the last row in column K Code: -------------------- Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343") -------------------- KDG;434485 Wrote: I am attempting to apply a macro to a file I download and edit each month. Each month there are more records (rows) in this file. This being so, a portion of the macro is attempting to move around some of the data to format the data consistantly. However, when I have done this it is selecting the same range each time. I would like it to know that when I go to the bottom of the column and then down one row and over 3 rows that this is where I wish it to select my new range to move, no matter where the end.down leads me to. Here is the portion of the code that I'm attempting to change. --- Code: -------------------- Range("K2").Select Selection.End(xlDown).Select Range("H2343:J2343").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("I2343:K9184") Range("K9184").Select Selection.End(xlUp).Select Range("I6902:J6902").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("J6902:K9184") -------------------- As you see, the RANGE("...").Select areas are the portions I'm needing to be intelligent enough to know that it's not always that cell that I will be selecting. The cell will always be one row below the row that I end up on when I do the Selection.End(xlDown).Select and then either two or three cells to the left. then they will be moved over one cell. Would this be something like the "RC[-3]" code? I am doing this through the macro recorder and am rather a novice into the actual coding of macros. I hope that this makes sense. Any help would be greatly appreciated!!! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'macro code selecting moving range - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=120622) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
Can you supply a sample workbook with your code and a couple of sheets one showing the before and one showing after? You can join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachments to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this query can continue to do so. :) KDG;435867 Wrote: ok... that helped figure out what was going on there... thanks bunches. Now... two more things. Seems you solve one thing and something else pops up! 1) In order for it to see the correct sheet name I'll have to manually change the sheet name to the name in the code. Right? or have the code recognize the current sheet name no matter what it is? Could this be coded in? I'm assuming so since I know that nothing is impossible, just beyond my experience. 2) Now... when I get to the part of the code that is selecting a range (which is different each time) and moving it to the right one space I get error because it's saying that the selected range and the destination size do not match. I understand what it's saying, but I don't know how to tell it to accept what I'm wanting it to do. here's the code that is the problem... ActiveCell.Offset(1, -3).Range("A1:C1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=ActiveCell.Offset(0, 1).Range("A1:C6842") <--this line and the error code is... Run Time error 1004 The information cannot be pasted because the cut area and the paste area are not the same size and shape. Try one of these: click a single cell and then past or select a rectangle that's the same size and shape, and then paste. ?????????????? Thanks so much for your help. I'm learning TONS!!!! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
ok... am I doing this right? I'm assuming I'll get an e-mail alert when you reply. Thanks for leading me this direction. Don't laugh at my logic, please. ok. Here are the files. The one with the data that will be run through it's paces [LCLI.xls] and the one that contains the macro [PERSONAL.xls]. I keep them in the personal macro file because the download of the txt file from our system is converted to the LCLI.xls file and it's just easier for me to pull the macro from the other file... it makes sense to me. on a personal note... I see you're from England. I just got back from 9 wonderful days in London. It was awesome! I can't wait to go back! KDG;434485 Wrote: I am attempting to apply a macro to a file I download and edit each month. Each month there are more records (rows) in this file. This being so, a portion of the macro is attempting to move around some of the data to format the data consistantly. However, when I have done this it is selecting the same range each time. I would like it to know that when I go to the bottom of the column and then down one row and over 3 rows that this is where I wish it to select my new range to move, no matter where the end.down leads me to. Here is the portion of the code that I'm attempting to change. --- Code: -------------------- Range("K2").Select Selection.End(xlDown).Select Range("H2343:J2343").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("I2343:K9184") Range("K9184").Select Selection.End(xlUp).Select Range("I6902:J6902").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=Range("J6902:K9184") -------------------- As you see, the RANGE("...").Select areas are the portions I'm needing to be intelligent enough to know that it's not always that cell that I will be selecting. The cell will always be one row below the row that I end up on when I do the Selection.End(xlDown).Select and then either two or three cells to the left. then they will be moved over one cell. Would this be something like the "RC[-3]" code? I am doing this through the macro recorder and am rather a novice into the actual coding of macros. I hope that this makes sense. Any help would be greatly appreciated!!! -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
KDG, you have posted but not the workbooks, if you are uploading personal.xls please rename it before uploading to prevent people actually changing theirs accidentally. Attachments. To upload a workbook, click reply then add your few words, scroll down past the submit button and you will see the Manage Attachments button, this is where you get to add files for upload, if you have any trouble please use this link or the one at the bottom of the any page. KDG;436184 Wrote: ok... am I doing this right? I'm assuming I'll get an e-mail alert when you reply. Thanks for leading me this direction. Don't laugh at my logic, please. ok. Here are the files. The one with the data that will be run through it's paces [LCLI.xls] and the one that contains the macro [PERSONAL.xls]. I keep them in the personal macro file because the download of the txt file from our system is converted to the LCLI.xls file and it's just easier for me to pull the macro from the other file... it makes sense to me. on a personal note... I see you're from England. I just got back from 9 wonderful days in London. It was awesome! I can't wait to go back! -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
ok... I'll get the hang of it. +-------------------------------------------------------------------+ |Filename: LCLI.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=199| +-------------------------------------------------------------------+ -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
KDG;436350 Wrote: ok... I'll get the hang of it.Are you opening the workbook called LCLI? or has the workbook you ar working with got a sheet called LCLI?, because thats your problem, you code (albeit a bit lengthy) works fine, you just are pointing it to wor on a workbook that isn't active or if it is it does not contain th worksheet LCLI Can you clarify -- Simon Lloy Regards Simon Lloy 'Microsoft Office Help' (http://www.thecodecage.com ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid= View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=12062 |
macro code selecting moving range
Simon Lloyd;436383 Wrote: Are you opening the workbook called LCLI? or has the workbook you are working with got a sheet called LCLI?, because thats your problem, your code (albeit a bit lengthy) works fine, you just are pointing it to work on a workbook that isn't active or if it is it does not contain the worksheet LCLI. Can you clarify?As a test you can add this to the very top of your code after Sub LCLI_NEW Code: -------------------- Dim ShFound As String, MySht As Long For Each Sheet In ActiveWorkbook.Sheets If Sheet.Name = "LCLI" Then MySht = 1 End If ShFound = ShFound & vbLf & Sheet.Name Next Sheet MsgBox "Sheets present in workbook" & vbLf & ShFound, vbOKOnly, "Sheets Available" If MySht < 1 Then Exit Sub -------------------- this code will show you the names of all the sheets available in the active workbook and will exit your code if the sheet you want to work with is not present! -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
Simon Lloyd;436383 Wrote: Are you opening the workbook called LCLI? or has the workbook you are working with got a sheet called LCLI?, because thats your problem, your code (albeit a bit lengthy) works fine, you just are pointing it to work on a workbook that isn't active or if it is it does not contain the worksheet LCLI. Can you clarify? Actually... it's a file (workbook) named LCLI.xls that has a worksheet also named LCLI. Is that my problem??? What are my options??? -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
KDG;436414 Wrote: Actually... it's a file (workbook) named LCLI.xls that has a worksheet also named LCLI. Is that my problem??? What are my options??? If thats the case then change The words ActiveWorkbook in your code for Workbooks("LCLI").Sheets("LCLI")..... and you should be good to go..or simply at the top of your code (as long as the workbook is open) Code: -------------------- Workbooks("LCLI").Activate Workbooks("LCLI").Sheets("LCLI").Activate -------------------- Then you won't experience another problem or need the code i supplied in the previous post :) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
Simon Lloyd;436429 Wrote: If thats the case then change The words ActiveWorkbook in your code for Workbooks("LCLI").Sheets("LCLI")..... and you should be good to go..or simply at the top of your code (as long as the workbook is open) Code: -------------------- Workbooks("LCLI").Activate Workbooks("LCLI").Sheets("LCLI").Activate -------------------- Then you won't experience another problem or need the code i supplied in the previous post :) On checking your code further you WILL need to use Code: -------------------- Workbooks("LCLI").Activate Workbooks("LCLI").Sheets("LCLI").Activate -------------------- at the top of your code otherwise things like Range("B2").Select will still be in the Personal workbook rather than the workbook you mean! -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
buggers.... that doesn't seem to work. I just copied what you had as the first two lines of the code and it stopped right there. I'm being a real pest. Don't you wish I'd never found you :ill? I know that this has to be something that should be very simple (as I really don't know what I'm doing most of the time when it comes to macro writing... I'm brilliant in other areas, but not in this!). Any other ideas? I'll keep playing. -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
KDG;436475 Wrote: buggers.... that doesn't seem to work. I just copied what you had as the first two lines of the code and it stopped right there. I'm being a real pest. Don't you wish I'd never found you :ill? I know that this has to be something that should be very simple (as I really don't know what I'm doing most of the time when it comes to macro writing... I'm brilliant in other areas, but not in this!). Any other ideas? I'll keep playing.if this Code: -------------------- Workbooks("LCLI").Activate Workbooks("LCLI").Sheets("LCLI").Activate -------------------- doesn't work then the workbook either isn't open or the name has a typo or something! Are you sure the workbook is open? and the name is LCLI with no leading or trailing space? -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
Simon Lloyd;436482 Wrote: if this Code: -------------------- Workbooks("LCLI").Activate Workbooks("LCLI").Sheets("LCLI").Activate -------------------- doesn't work then the workbook either isn't open or the name has a typo or something! Are you sure the workbook is open? and the name is LCLI with no leading or trailing space?Change the top of your code to this to open the workbook (don't forget to change the path) Code: -------------------- Sub LCLI_NEW() ' ' LCLI_NEW Macro ' Format captured LCLI file for import to Access Client DB; 7/29/09 KDG ' ' Keyboard Shortcut: Ctrl+Shift+K ' Workbooks.Open ("C:\Users\KDG\Documents\LCLI.xls") 'change the path Workbooks("LCLI").Activate Workbooks("LCLI").Sheets("LCLI").Activate Range("B1").Select '.....etc -------------------- -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
Yep... I went back and made sure that the file name is most assuredly LCLI.xls and that the worksheet name is LCLI no spaces. I have tried to run the macro while in the LCLI file and also while still in the PERSONAL file and neither works. I understand what you're saying and it all does make sense, but for some reason it doesn't work. Maybe it's because my desk is full of a bunch of other junk that is frustrating me also! Tough day... at least yours is almost over! I still have another hour & 1/2 at work, then home for more work!!! You have my files... does it work when you add those lines? The only thing left is for me to shut down and reboot and see if my computer is just being stupid. -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
KDG;436497 Wrote: Yep... I went back and made sure that the file name is most assuredly LCLI.xls and that the worksheet name is LCLI no spaces. I have tried to run the macro while in the LCLI file and also while still in the PERSONAL file and neither works. I understand what you're saying and it all does make sense, but for some reason it doesn't work. Maybe it's because my desk is full of a bunch of other junk that is frustrating me also! Tough day... at least yours is almost over! I still have another hour & 1/2 at work, then home for more work!!! You have my files... does it work when you add those lines? The only thing left is for me to shut down and reboot and see if my computer is just being stupid.Its not your computer and the code worked for me....as one last test put all that code in another (fresh) workbook, and and make sure the vbe is open, make sure that you currently have LCLI active then run the code in the vbe...it should work fine, let me know the results and i'll see what we can do from there! -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
I changed it to.. Workbooks("LCLI.xls").Activate Workbooks("LCLI.xls").Sheets("LCLI").Activate and it got past that line, but further down at this point, it errored out, too.... ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear and I'm sure that this is a "simple" fix for minds that know what they're doing <you and not for those of us who are still struggling <me!.:confused: And I'm sure that whatever change is made in this line is likely needed in the other like lines that follow in the sort lines. Am I right or way off base? -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
KDG;436514 Wrote: I changed it to.. Workbooks("LCLI.xls").Activate Workbooks("LCLI.xls").Sheets("LCLI").Activate and it got past that line, but further down at this point, it errored out, too.... ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear and I'm sure that this is a "simple" fix for minds that know what they're doing <you and not for those of us who are still struggling <me!.:confused: And I'm sure that whatever change is made in this line is likely needed in the other like lines that follow in the sort lines. Am I right or way off base?Last try for today as im signing off, it was my fault i forgot the .xls form the workbook name but this line ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFie lds.Clear should not have the .XLS as you are working with the worksheet which is an object within the .XLS -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
aahhhh!!!! I'll give that a shot! Thanks so much for all your patience and persistence! You're a doll!!! Good night! I'll post a thread to let you know how it goes. -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
That destination is a fixed size if you are always pasting 3 coulmns of data and it will always be the Activecell.offset(0, 1) then loose the Range("A1:C6842") as all that does is set the size of the paste area not the destination, the destination cell is your activecell.offset. Does that help? KDG;436556 Wrote: aahhhh!!!! I'll give that a shot! Thanks so much for all your patience and persistence! You're a doll!!! Good night! I'll post a thread to let you know how it goes. Ok.. so I tried it (taking out the .xls from those lines and any subsequent lines). That got me back to my original question that I had before I sent the files to you. I'm still erroring out at the red line he Selection.End(xlDown).Select ActiveCell.Offset(1, -3).Range("A1:C1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Destination:=ActiveCell.Offset(0, 1).Range("A1:C6842") Each time this is run, the cut range will differ, therefore the destination range cannot be static, either. So.... ???? Hope you have a great nite and a wonderful Friday morning! I will be working on printing and preparing a distribution of a newsletter to our clients in the morning, so you have plenty of time to help others in probably more need than I... Again, you don't know how much I appreciate not only your help, but how much I have learned! Thank you, thank you, thank you!!! -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
WOW!!!! I think we got it!!!! Yes! I've run it on three different downloads and so far, so good! I think I've gotten it to where I need it to be! Of course I couldn't have done it without you!!! YYYAAAAA!!!!! I can stop bugging you today! It's Friday, it's afternoon (evening for you) and I can say that I've really improved my work life by this one little thing. And with what I've learned from you, I can apply some of this on another similar file that I do each month, also. My hopes are to someday take what used to take my predicesor a month to do manually and make it into a simple, no-brainer, one-day task. I've already had it down to about 4-5 days, depending on the changes to our client listing and this will chip away at that. THANK YOU SOOOOO MUCH!!!! and have a WONDERFUL weekend!!!! Kathryn G. Moline, IL, USA -- KDG ------------------------------------------------------------------------ KDG's Profile: http://www.thecodecage.com/forumz/member.php?userid=590 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
macro code selecting moving range
KDG;437595 Wrote: WOW!!!! I think we got it!!!! Yes! I've run it on three different downloads and so far, so good! I think I've gotten it to where I need it to be! Of course I couldn't have done it without you!!! YYYAAAAA!!!!! I can stop bugging you today! It's Friday, it's afternoon (evening for you) and I can say that I've really improved my work life by this one little thing. And with what I've learned from you, I can apply some of this on another similar file that I do each month, also. My hopes are to someday take what used to take my predicesor a month to do manually and make it into a simple, no-brainer, one-day task. I've already had it down to about 4-5 days, depending on the changes to our client listing and this will chip away at that. THANK YOU SOOOOO MUCH!!!! and have a WONDERFUL weekend!!!! Kathryn G. Moline, IL, USA Glad we could be of help! -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120622 |
| All times are GMT +1. The time now is 10:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com