Help with a macro

[Wombat]

This is what I have so far. It comes up with an error 1004 message as soon as
I put the connection to the second worksheet to paste the information... I
dont understand :-(

Any help much appreciated!


Sub Wombat()

Sheets("1").Cells(3, 12).Select
x = 3
y = 24
Do Until Cells(x, 12).Value = "END"

If Cells(x, 12).Value <> "" Then

Cells(x, 12).Copy

Sheets("2").Cells(y, 1).Select

ActiveSheet.Paste

Sheets("1").Cells(3, 12).Select

x = x + 1
y = y + 1

Else

x = x + 1

End If

Loop

End Sub

 

[Dave Peterson]

When you use this syntax:
Sheets("1").Cells(...)
Excel is looking for a sheet with the name 1--not the first sheet in the
workbook.

So my guess is that you really wanted:
Sheets(1).cells(...)
and
Sheets(2).cells(...)

But it could be that you did want to use the sheets named 1 and 2. And I'd bet
that you don't have a sheet named 2 (look for a trailing space character to
check).

These would represent the first two (starting from the left) sheets in your
workbook.

Am I close?

If you wanted to be more specific, you could refer to the names:
Sheets("SomeSheetNameHere").cells(...)
and
sheets("someothersheetnamehere").cells(...)

 

[Roger Govier]

Hi

It is failing because to need to either Activate a Sheet, before you can
select or activate a cell.

There is no need to be selecting sheets however, you can just do a
straight copy from Sheet 1 to Sheet 2 as in the code below

Sub Wombat()

Sheets("1").Cells(3, 12).Select
x = 3
y = 24
Do Until Cells(x, 12).Value = "END"
If Cells(x, 12).Value <> "" Then
Cells(x, 12).Copy Sheets("2").Cells(y, 1)
x = x + 1
y = y + 1
Else
x = x + 1
End If
Loop

End Sub

 

[Mike H]

Hi,

Try it like this

Sub Wombat()
Dim x As Long, y As Long
Set sht1 = Sheets("1")
Set Sht2 = Sheets("2")
x = 3
y = 24
Do Until UCase(sht1.Cells(x, 12).Value) = "END"
If sht1.Cells(x, 12).Value <> "" Then
Sht2.Cells(y, 1) = sht1.Cells(x, 12)
x = x + 1
y = y + 1
Else
x = x + 1
End If
Loop
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[LarryP]

Are your sheets actually NAMED 1 and 2, in which case the quotes are
appropriate, or are you trying to refer to them by their sequence number in
the Sheets collection? If the latter, I'm not a big Excel guru but I think
Sheets(1) is the way to go.