Thread: Help, Code won't work and I have no clue

I am trying to take data from from a Sheet called %Efficiency and past it
into a sheet Called MMM11. I the problem is in my selecion of the the range
to copy. I don't know why it won't work. The only problem that i was
thinking it could be is that cells A1-C1 do not contain a date where as the
rest of the row 1 does. I don't know though please help.

Dim X As Integer, Y As Integer
For X = 1 To 100
If Cells(1, X) < Date Then Y = X
Next
Sheets("%Efficiency").Select
Selection.Range(Cells(1, Y + 1), Cells(3, Y - 19)).Select
Selection.Copy
Sheets("MMM11").Select
ActiveSheet.Range("B19").Select
Selection.PasteSpecial (xlPasteValuesAndNumberFormats)

Not tested, but probably

Dim X As Integer, Y As Integer
For X = 1 To 100
If Cells(1, X) < Date Then Y = X
Next
Sheets("%Efficiency").Select
Range(Cells(1, Y + 1), Cells(3, Y - 19)).Copy
Sheets("MMM11").Select
ActiveSheet.Range("B19").Select
Selection.PasteSpecial (xlPasteValuesAndNumberFormats)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Lost and Looking for Help"
<LostandLookingforHelp@discussions.microsoft.com> wrote in message
news:595C9D07-D04B-42E5-BA2C-D03BE7A3B322@microsoft.com...
> I am trying to take data from from a Sheet called %Efficiency and past it
> into a sheet Called MMM11. I the problem is in my selecion of the the
range
> to copy. I don't know why it won't work. The only problem that i was
> thinking it could be is that cells A1-C1 do not contain a date where as
the
> rest of the row 1 does. I don't know though please help.
>
> Dim X As Integer, Y As Integer
> For X = 1 To 100
> If Cells(1, X) < Date Then Y = X
> Next
> Sheets("%Efficiency").Select
> Selection.Range(Cells(1, Y + 1), Cells(3, Y - 19)).Select
> Selection.Copy
> Sheets("MMM11").Select
> ActiveSheet.Range("B19").Select
> Selection.PasteSpecial (xlPasteValuesAndNumberFormats)

when you do select.Range

then you are anchoring your range to the currently active cell and
offsetting from there.

so (as an example)
Range("Z1").Select
Selection.Range("A1:C1").copy

would copy Z1:AB1

I doubt that is your intent. Try:

Dim X As Integer, Y As Integer
With Sheets("%Efficiency")
For X = 1 To 100
If .Cells(1, X) < Date Then
Y = X
end if
Next
.Range(.Cells(1, Y + 1), .Cells(3, Y - 19)).Copy
End With
Sheets("MMM11").Range("B19").PasteSpecial _
xlPasteValuesAndNumberFormats
--
Regards,
Tom Ogilvy



"Lost and Looking for Help" wrote:

> I am trying to take data from from a Sheet called %Efficiency and past it
> into a sheet Called MMM11. I the problem is in my selecion of the the range
> to copy. I don't know why it won't work. The only problem that i was
> thinking it could be is that cells A1-C1 do not contain a date where as the
> rest of the row 1 does. I don't know though please help.
>
> Dim X As Integer, Y As Integer
> For X = 1 To 100
> If Cells(1, X) < Date Then Y = X
> Next
> Sheets("%Efficiency").Select
> Selection.Range(Cells(1, Y + 1), Cells(3, Y - 19)).Select
> Selection.Copy
> Sheets("MMM11").Select
> ActiveSheet.Range("B19").Select
> Selection.PasteSpecial (xlPasteValuesAndNumberFormats)

I don't understand this part. If on sheet %efficiency you can delete
"Sheets("%Efficiency")."
For X = 1 To 100
If Cells(1, X) < Date Then Y = X
Next
======maybe

y=cells(1,256).end(xltoleft).column
'then withOUT selections
Sheets("%Efficiency").Range(Cells(1, Y + 1), Cells(3, Y - 19)).Copy
Sheets("MMM11").Range("B19").PasteSpecial (xlPasteValuesAndNumberFormats)

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Lost and Looking for Help"
<LostandLookingforHelp@discussions.microsoft.com> wrote in message
news:595C9D07-D04B-42E5-BA2C-D03BE7A3B322@microsoft.com...
>I am trying to take data from from a Sheet called %Efficiency and past it
> into a sheet Called MMM11. I the problem is in my selecion of the the
> range
> to copy. I don't know why it won't work. The only problem that i was
> thinking it could be is that cells A1-C1 do not contain a date where as
> the
> rest of the row 1 does. I don't know though please help.
>
> Dim X As Integer, Y As Integer
> For X = 1 To 100
> If Cells(1, X) < Date Then Y = X
> Next
> Sheets("%Efficiency").Select
> Selection.Range(Cells(1, Y + 1), Cells(3, Y - 19)).Select
> Selection.Copy
> Sheets("MMM11").Select
> ActiveSheet.Range("B19").Select
> Selection.PasteSpecial (xlPasteValuesAndNumberFormats)