macro code selecting moving range

[KDG]

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

 

[Simon Lloyd]

Assuming you are copying from H2 to the last row in column
Code
-------------------

Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343"

-------------------

I am attempting to apply a macro to a file I download and edit eac
month
Each month there are more records (rows) in this file. This being so,
portion of the macro is attempting to move around some of the data t
forma
the data consistantly. However, when I have done this it is selectin
th
same range each time. I would like it to know that when I go to th
bottom o
the column and then down one row and over 3 rows that this is where
wish i
to select my new range to move, no matter where the end.down leads m
to
Here is the portion of the code that I'm attempting to change
-- Code
-------------------Range("K2").Selec
Selection.End(xlDown).Selec
Range("H2343:J2343").Selec
Range(Selection, Selection.End(xlDown)).Selec
Selection.Cut Destination:=Range("I2343:K9184"
Range("K9184").Selec
Selection.End(xlUp).Selec
Range("I6902:J6902").Selec
Range(Selection, Selection.End(xlDown)).Selec
Selection.Cut Destination:=Range("J6902:K9184"
-------------------As you see, the RANGE("...").Select areas are the portions I'
needing to b
intelligent enough to know that it's not always that cell that I wil
b
selecting. The cell will always be one row below the row that I en
up o
when I do the Selection.End(xlDown).Select and then either two o
three cell
to the left. then they will be moved over one cell. Would this b
somethin
like the "RC[-3]" code? I am doing this through the macro recorde
and a
rather a novice into the actual coding of macros

I hope that this makes sense. Any help would be greatl
appreciated!!

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[KDG]

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!

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

--------------------

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)

 

[KDG]

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.SortFields.Clear
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.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

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!

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

--------------------

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]

The error 9 is usually because excel cannot find the sheet you ar
asking it to look for try this and see what sheet names it gives you, i
will give you the activeworkbook name and the activesheet name to prov
what it is you are working with
Code
-------------------

msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.nam
Cells.Selec
ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFields.Clea
msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.nam
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
With ActiveWorkbook.Worksheets("LCLI.xls").Sor
.SetRange Columns("A:K"
.Header = xlYe
.MatchCase = Fals
.Orientation = xlTopToBotto
.SortMethod = xlPinYi
.Appl
End Wit
-------------------

I have solved my last question... but now I have another. I had th
entir
macro up and running on the file that I was working with. But as
stated,
run a new download each month that will contain more records each time

always open the file into Excel and save it as "LCLI.xls" To test it,
ra
a new download to see if it would work. I get an error 9: subscript ou
o
range and the debugger looks like this... the second line i
highlighted
What am i not telling it to do???
Code
-------------------Cells.Selec
ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFields.Clea
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range(
"H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending
DataOption:=
xlSortNorma
With ActiveWorkbook.Worksheets("LCLI.xls").Sor
.SetRange Columns("A:K"
.Header = xlYe
.MatchCase = Fals
.Orientation = xlTopToBotto
.SortMethod = xlPinYi
.Appl
End Wit
-------------------"KDG" wrote

OK! Thanks! I think I have this thing almost figured out. Now.. the las
thing I need for this thing to do is to delete anything tha appears belo
the last record (by going to the last cell in column A an selecting all th
rows below that and deleting them). Here's what the code i

erroring out on..

ActiveCell.Offset(-1, 1).Range("A1").Selec
Selection.End(xlDown).Selec
ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Selec
Selection.ClearContent

I'm assuming that it has something to do with the third line. An hints??

Thanks you SOOO much for your help and your very speedy reply! I has mad
my day

0).Row).Copy Destination:=Range("I2343"

-------------------

KDG;434485 Wrote
I am attempting to apply a macro to a file I download and edi 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/showthread.php?t=120622)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[KDG]

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

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.SortFields.Clear
msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.name
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.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
--------------------

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.SortFields.Clear
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.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
--------------------

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


Assuming you are copying from H2 to the last row in column K
Code:

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' (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/showthread.php?t=120622)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Simon Lloyd]

Can you supply a sample workbook with your code and a couple of sheet
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 you
posts so you can add workbooks to better illustrate your problems an
get help directly with them. Also if you do join please post in thi
thread (link found below) so that people who have been following o
helping with this query can continue to do so. :

ok... that helped figure out what was going on there... thanks bunches
Now... two more things. Seems you solve one thing and something els
pops up

1) In order for it to see the correct sheet name I'll have to manuall
change the sheet name to the name in the code. Right? or have the cod
recognize the current sheet name no matter what it is? Could this b
code
in? I'm assuming so since I know that nothing is impossible, jus
beyond m
experience

2) Now... when I get to the part of the code that is selecting a rang
(which is different each time) and moving it to the right one space
ge
error because it's saying that the selected range and the destinatio
siz
do not match. I understand what it's saying, but I don't know how t
tell i
to accept what I'm wanting it to do

here's the code that is the problem..
ActiveCell.Offset(1, -3).Range("A1:C1").Selec
Range(Selection, Selection.End(xlDown)).Selec
Selection.Cut Destination:=ActiveCell.Offset(0, 1).Range("A1:C6842"
<--this lin

and the error code is..
Run Time error 100
The information cannot be pasted because the cut area and the past
area ar
not the same size and shape. Try one of these: click a single cell an
the
past or select a rectangle that's the same size and shape, and the
paste

?????????????? Thanks so much for your help. I'm learning TONS!!!

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[KDG]

ok... am I doing this right? I'm assuming I'll get an e-mail alert whe
you reply. Thanks for leading me this direction. Don't laugh at m
logic, please.

ok. Here are the files. The one with the data that will be run throug
it's paces [LCLI.xls] and the one that contains the macr
[PERSONAL.xls]. I keep them in the personal macro file because th
download of the txt file from our system is converted to the LCLI.xl
file and it's just easier for me to pull the macro from the othe
file... it makes sense to me.

I am attempting to apply a macro to a file I download and edit eac
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 t
format
the data consistantly. However, when I have done this it is selectin
the
same range each time. I would like it to know that when I go to th
bottom of
the column and then down one row and over 3 rows that this is where
wish it
to select my new range to move, no matter where the end.down leads m
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'
needing to be
intelligent enough to know that it's not always that cell that I wil
be
selecting. The cell will always be one row below the row that I en
up on
when I do the Selection.End(xlDown).Select and then either two o
three cells
to the left. then they will be moved over one cell. Would this b
something
like the "RC[-3]" code? I am doing this through the macro recorde
and am
rather a novice into the actual coding of macros.

I hope that this makes sense. Any help would be greatl
appreciated!!

 

[Simon Lloyd]

KDG, you have posted but not the workbooks, if you are uploadin
personal.xls please rename it before uploading to prevent peopl
actually changing theirs accidentally
Attachments.

To upload a workbook, click reply then add your few words, scroll dow
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 troubl
please use this link or the one at the bottom of th
any page.

ok... am I doing this right? I'm assuming I'll get an e-mail alert whe
you reply. Thanks for leading me this direction. Don't laugh at m
logic, please

ok. Here are the files. The one with the data that will be run throug
it's paces [LCLI.xls] and the one that contains the macr
[PERSONAL.xls]. I keep them in the personal macro file because th
download of the txt file from our system is converted to the LCLI.xl
file and it's just easier for me to pull the macro from the othe
file... it makes sense to me

on a personal note... I see you're from England. I just got back from
wonderful days in London. It was awesome! I can't wait to go back

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[KDG]

ok... I'll get the hang of it

+-------------------------------------------------------------------
|Filename: LCLI.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=199
+-------------------------------------------------------------------

 

[Simon Lloyd]

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]

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?As a test you can add this to the very top of your code after Su

LCLI_NE

Code
-------------------
Dim ShFound As String, MySht As Lon
For Each Sheet In ActiveWorkbook.Sheet
If Sheet.Name = "LCLI" The
MySht =
End I
ShFound = ShFound & vbLf & Sheet.Nam
Next Shee
MsgBox "Sheets present in workbook" & vbLf & ShFound, vbOKOnly, "Sheets Available
If MySht <> 1 Then Exit Su
-------------------
this code will show you the names of all the sheets available in th
active workbook and will exit your code if the sheet you want to wor
with is not present

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[KDG]

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?

Actually... it's a file (workbook) named LCLI.xls that has a workshee
also named LCLI. Is that my problem??? What are my options??

 

[Simon Lloyd]

Actually... it's a file (workbook) named LCLI.xls that has a workshee
also named LCLI. Is that my problem??? What are my options??

If thats the case then change The words ActiveWorkbook in your code fo
Workbooks("LCLI").Sheets("LCLI")..... and you should be good to go..o
simply at the top of your code (as long as the workbook is open)
Code
-------------------

Workbooks("LCLI").Activat
Workbooks("LCLI").Sheets("LCLI").Activat

-------------------
Then you won't experience another problem or need the code i supplied i
the previous post :

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Simon Lloyd]

If thats the case then change The words ActiveWorkbook in your code fo
Workbooks("LCLI").Sheets("LCLI")..... and you should be good to go..o
simply at the top of your code (as long as the workbook is open) > Code

On checking your code further you WILL need to use
Code
-------------------

Workbooks("LCLI").Activat
Workbooks("LCLI").Sheets("LCLI").Activat

-------------------
at the top of your code otherwise things like Range("B2").Select wil
still be in the Personal workbook rather than the workbook you mean

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[KDG]

buggers.... that doesn't seem to work. I just copied what you had as th
first two lines of the code and it stopped right there. I'm being a rea
pest. Don't you wish I'd never found you :ill? I know that this has t
be something that should be very simple (as I really don't know what I'
doing most of the time when it comes to macro writing... I'm brillian
in other areas, but not in this!). Any other ideas? I'll keep playing

 

[Simon Lloyd]

buggers.... that doesn't seem to work. I just copied what you had as th
first two lines of the code and it stopped right there. I'm being a rea
pest. Don't you wish I'd never found you :ill? I know that this has t
be something that should be very simple (as I really don't know what I'
doing most of the time when it comes to macro writing... I'm brillian
in other areas, but not in this!). Any other ideas? I'll keep playing.if thi

Code
-------------------
Workbooks("LCLI").Activat
Workbooks("LCLI").Sheets("LCLI").Activat
-------------------
doesn't work then the workbook either isn't open or the name has a typ
or something

Are you sure the workbook is open? and the name is LCLI with no leadin
or trailing space

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Simon Lloyd]

if this> Code
-------------------
typo or something

Are you sure the workbook is open? and the name is LCLI with n
leading or trailing space?Change the top of your code to this to open the workbook (don't forge

to change the path

Code
-------------------
Sub LCLI_NEW(

' LCLI_NEW Macr
' Format captured LCLI file for import to Access Client DB; 7/29/09 KD

' Keyboard Shortcut: Ctrl+Shift+

Workbooks.Open ("C:\Users\KDG\Documents\LCLI.xls"
'change the pat
Workbooks("LCLI").Activat
Workbooks("LCLI").Sheets("LCLI").Activat
Range("B1").Select '.....et
-------------------

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[KDG]

Yep... I went back and made sure that the file name is most assuredl
LCLI.xls and that the worksheet name is LCLI no spaces. I have tried t
run the macro while in the LCLI file and also while still in th
PERSONAL file and neither works. I understand what you're saying and i
all does make sense, but for some reason it doesn't work. Maybe it'
because my desk is full of a bunch of other junk that is frustrating m
also! Tough day... at least yours is almost over! I still have anothe
hour & 1/2 at work, then home for more work!!!

You have my files... does it work when you add those lines? The onl
thing left is for me to shut down and reboot and see if my computer i
just being stupid

 

[Simon Lloyd]

Yep... I went back and made sure that the file name is most assuredl
LCLI.xls and that the worksheet name is LCLI no spaces. I have tried t
run the macro while in the LCLI file and also while still in th
PERSONAL file and neither works. I understand what you're saying and i
all does make sense, but for some reason it doesn't work. Maybe it'
because my desk is full of a bunch of other junk that is frustrating m
also! Tough day... at least yours is almost over! I still have anothe
hour & 1/2 at work, then home for more work!!

You have my files... does it work when you add those lines? The onl
thing left is for me to shut down and reboot and see if my computer i
just being stupid.Its not your computer and the code worked for me....as one last test pu

all that code in another (fresh) workbook, and and make sure the vbe i
open, make sure that you currently have LCLI active then run the code i
the vbe...it should work fine, let me know the results and i'll see wha
we can do from there

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com