I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

Thanks for the bery fast response BUT...here is the code I used
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy
Destination = Worksheets("compare").Range("A1")

End Sub

When the macro got to rng Offset(0,-1)Resize(15,2).Copy
I got the following error message

Run time error "1004"
Application-defined or object -defined error

Help
Ian M


"Tom Ogilvy" wrote in message
...
Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

hi,
Sub macCopyTo()
Sheets("sheet1").Select
Range("IV1").End(xlToLeft).Select
Range(ActiveCell, ActiveCell.Offset(15, -1)).Copy
Sheets("sheet2").Select
Range("A1").Select
Selection.PasteSpecial xlPasteAll
Msgbox ("Done")
End Sub

Regards
FSt1

"PCOR" wrote:

I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

In contrast, the code that I posted:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

worked superbly. (yours did have an error, but I don't think you reached it
yet)

this would suggest you don't have at least 2 columns of data which violates
what you described, or you are using excel 97 in which case you should set
the takefocusonclick property of the commandbutton to false (or both).

to account for anomalies:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
if rng.column = 1 then
msgbox "Must have at least column A and B"
exit sub
'or
' Rng.Resize(15,1).copy _
' Destination:=worksheets("compare").Range("A1")
Else
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End if

End Sub


--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Thanks for the bery fast response BUT...here is the code I used
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy
Destination = Worksheets("compare").Range("A1")

End Sub

When the macro got to rng Offset(0,-1)Resize(15,2).Copy
I got the following error message

Run time error "1004"
Application-defined or object -defined error

Help
Ian M


"Tom Ogilvy" wrote in message
...
Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

Hi
You hit it right on the head.....my data is not continuous.
Here is the actual layout
Starts at B9 down to b32 and across to K9/k32
But next week I will be adding l9 to l32
Can you please give me the code for this situation....sorry for the screw-up

"Tom Ogilvy" wrote in message
...
In contrast, the code that I posted:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

worked superbly. (yours did have an error, but I don't think you reached
it
yet)

this would suggest you don't have at least 2 columns of data which
violates
what you described, or you are using excel 97 in which case you should set
the takefocusonclick property of the commandbutton to false (or both).

to account for anomalies:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
if rng.column = 1 then
msgbox "Must have at least column A and B"
exit sub
'or
' Rng.Resize(15,1).copy _
' Destination:=worksheets("compare").Range("A1")
Else
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End if

End Sub


--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Thanks for the bery fast response BUT...here is the code I used
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy
Destination = Worksheets("compare").Range("A1")

End Sub

When the macro got to rng Offset(0,-1)Resize(15,2).Copy
I got the following error message

Run time error "1004"
Application-defined or object -defined error

Help
Ian M


"Tom Ogilvy" wrote in message
...
Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

Yes, I guess you tricked me when you originally said:

I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(9, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(24, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

would be my guess based on the new revelations.

--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Hi
You hit it right on the head.....my data is not continuous.
Here is the actual layout
Starts at B9 down to b32 and across to K9/k32
But next week I will be adding l9 to l32
Can you please give me the code for this situation....sorry for the
screw-up

"Tom Ogilvy" wrote in message
...
In contrast, the code that I posted:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

worked superbly. (yours did have an error, but I don't think you
reached
it
yet)

this would suggest you don't have at least 2 columns of data which
violates
what you described, or you are using excel 97 in which case you should
set
the takefocusonclick property of the commandbutton to false (or both).

to account for anomalies:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
if rng.column = 1 then
msgbox "Must have at least column A and B"
exit sub
'or
' Rng.Resize(15,1).copy _
' Destination:=worksheets("compare").Range("A1")
Else
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End if

End Sub


--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Thanks for the bery fast response BUT...here is the code I used
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy
Destination = Worksheets("compare").Range("A1")

End Sub

When the macro got to rng Offset(0,-1)Resize(15,2).Copy
I got the following error message

Run time error "1004"
Application-defined or object -defined error

Help
Ian M


"Tom Ogilvy" wrote in message
...
Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

FANTASTIC...works just great. I dont mean to push..but I could you one more
peice of help
The current command1.button1 is located on sheet "DATA"
How can the code be changed so that I could place the COMMAD button on a
sheet called COMPARE and it would still do what it is doing now.
Thanks very much

"Tom Ogilvy" wrote in message
...
Yes, I guess you tricked me when you originally said:

I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(9, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(24, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

would be my guess based on the new revelations.

--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Hi
You hit it right on the head.....my data is not continuous.
Here is the actual layout
Starts at B9 down to b32 and across to K9/k32
But next week I will be adding l9 to l32
Can you please give me the code for this situation....sorry for the
screw-up

"Tom Ogilvy" wrote in message
...
In contrast, the code that I posted:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

worked superbly. (yours did have an error, but I don't think you
reached
it
yet)

this would suggest you don't have at least 2 columns of data which
violates
what you described, or you are using excel 97 in which case you should
set
the takefocusonclick property of the commandbutton to false (or both).

to account for anomalies:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
if rng.column = 1 then
msgbox "Must have at least column A and B"
exit sub
'or
' Rng.Resize(15,1).copy _
' Destination:=worksheets("compare").Range("A1")
Else
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End if

End Sub


--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Thanks for the bery fast response BUT...here is the code I used
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy
Destination = Worksheets("compare").Range("A1")

End Sub

When the macro got to rng Offset(0,-1)Resize(15,2).Copy
I got the following error message

Run time error "1004"
Application-defined or object -defined error

Help
Ian M


"Tom Ogilvy" wrote in message
...
Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

Private Sub CommandButton1_Click()
Dim rng As Range
With worksheets("Data")
Set rng = .Cells(9, "IV").End(xlToLeft)
End With
rng.Offset(0, -1).Resize(24, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End Sub

--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
FANTASTIC...works just great. I dont mean to push..but I could you one
more
peice of help
The current command1.button1 is located on sheet "DATA"
How can the code be changed so that I could place the COMMAD button on a
sheet called COMPARE and it would still do what it is doing now.
Thanks very much

"Tom Ogilvy" wrote in message
...
Yes, I guess you tricked me when you originally said:

I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(9, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(24, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

would be my guess based on the new revelations.

--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Hi
You hit it right on the head.....my data is not continuous.
Here is the actual layout
Starts at B9 down to b32 and across to K9/k32
But next week I will be adding l9 to l32
Can you please give me the code for this situation....sorry for the
screw-up

"Tom Ogilvy" wrote in message
...
In contrast, the code that I posted:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

worked superbly. (yours did have an error, but I don't think you
reached
it
yet)

this would suggest you don't have at least 2 columns of data which
violates
what you described, or you are using excel 97 in which case you
should
set
the takefocusonclick property of the commandbutton to false (or
both).

to account for anomalies:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
if rng.column = 1 then
msgbox "Must have at least column A and B"
exit sub
'or
' Rng.Resize(15,1).copy _
' Destination:=worksheets("compare").Range("A1")
Else
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End if

End Sub


--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Thanks for the bery fast response BUT...here is the code I used
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy
Destination = Worksheets("compare").Range("A1")

End Sub

When the macro got to rng Offset(0,-1)Resize(15,2).Copy
I got the following error message

Run time error "1004"
Application-defined or object -defined error

Help
Ian M


"Tom Ogilvy" wrote in message
...
Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M

Many thanks...works great

"Tom Ogilvy" wrote in message
...
Private Sub CommandButton1_Click()
Dim rng As Range
With worksheets("Data")
Set rng = .Cells(9, "IV").End(xlToLeft)
End With
rng.Offset(0, -1).Resize(24, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End Sub

--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
FANTASTIC...works just great. I dont mean to push..but I could you one
more
peice of help
The current command1.button1 is located on sheet "DATA"
How can the code be changed so that I could place the COMMAD button on a
sheet called COMPARE and it would still do what it is doing now.
Thanks very much

"Tom Ogilvy" wrote in message
...
Yes, I guess you tricked me when you originally said:

I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(9, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(24, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

would be my guess based on the new revelations.

--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Hi
You hit it right on the head.....my data is not continuous.
Here is the actual layout
Starts at B9 down to b32 and across to K9/k32
But next week I will be adding l9 to l32
Can you please give me the code for this situation....sorry for the
screw-up

"Tom Ogilvy" wrote in message
...
In contrast, the code that I posted:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")

End Sub

worked superbly. (yours did have an error, but I don't think you
reached
it
yet)

this would suggest you don't have at least 2 columns of data which
violates
what you described, or you are using excel 97 in which case you
should
set
the takefocusonclick property of the commandbutton to false (or
both).

to account for anomalies:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
if rng.column = 1 then
msgbox "Must have at least column A and B"
exit sub
'or
' Rng.Resize(15,1).copy _
' Destination:=worksheets("compare").Range("A1")
Else
rng.Offset(0, -1).Resize(15, 2).Copy _
Destination:=Worksheets("compare").Range("A1")
End if

End Sub


--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Thanks for the bery fast response BUT...here is the code I used
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Cells(1, "IV").End(xlToLeft)
rng.Offset(0, -1).Resize(15, 2).Copy
Destination = Worksheets("compare").Range("A1")

End Sub

When the macro got to rng Offset(0,-1)Resize(15,2).Copy
I got the following error message

Run time error "1004"
Application-defined or object -defined error

Help
Ian M


"Tom Ogilvy" wrote in message
...
Dim rng as Range
set rng = Cells(1,"IV").End(xltoLeft)
rng.offset(0,-1).Resize(15,2).copy
Destination:=Worksheets("Sheet3").Range("A1")

--
Regards,
Tom Ogilvy

"PCOR" wrote in message
...
I currently have data in col A,B,C,D row 1 to 15 in each col
I was to copy the last two col(ie c1 to d15 to an other sheet
Now the catch.
Every week I add another col to this data.
So next week I will have COL a,b,c,d,e row 1 to 15 in each.
Now I want to copy Col d1 to e15 to an other sheet.
Need help with that
Thanks
Ian M