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!!!

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

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

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

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

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

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

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

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

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

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

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