Debug error 1004
 

Hi

I'm getting a debug error 1004 for this simpel code:
Error occurs in the Sheets line. I keep thinking theres one simpel
thing that I am missing, can anyone point me in the right direction?

Private Sub button2_Click()


Range("B18").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Original").Range("A9").Select
ActiveSheet.Paste

'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
':=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Debug error 1004
 

Hi.

Sheets("Original").Select
Range("A9").Select
HTH
Daniel

Hi

I'm getting a debug error 1004 for this simpel code:
Error occurs in the Sheets line. I keep thinking theres one simpel
thing that I am missing, can anyone point me in the right direction?

Private Sub button2_Click()


Range("B18").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Original").Range("A9").Select
ActiveSheet.Paste

'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
':=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Debug error 1004
 

Select the sheet then select the range...
Sheets("Original").Select
Sheets("Original").Range("A9").Select

Select only works on the active sheet.
--
Jim Cone
Portland, Oregon USA



"Kasper"

wrote in message
Hi
I'm getting a debug error 1004 for this simpel code:
Error occurs in the Sheets line. I keep thinking theres one simpel
thing that I am missing, can anyone point me in the right direction?

Private Sub button2_Click()
Range("B18").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Original").Range("A9").Select
ActiveSheet.Paste

'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
':=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Debug error 1004
 

On 27 Jan., 14:00, "Jim Cone" wrote:
Select the sheet then select the range...
Sheets("Original").Select
Sheets("Original").Range("A9").Select

Select only works on the active sheet.
--
Jim Cone
Portland, Oregon *USA

"Kasper"

wrote in message
Hi
I'm getting a debug error 1004 for this simpel code:
Error occurs in the Sheets line. I keep thinking theres one simpel
thing that I am missing, can anyone point me in the right direction?

Private Sub button2_Click()
Range("B18").Select
* * Range(Selection, Selection.End(xlToRight)).Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Selection.Copy
* * Sheets("Original").Range("A9").Select
* * ActiveSheet.Paste

* * 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * ':=False, Transpose:=False
* * Application.CutCopyMode = False
End Sub

Works like a charm!!! Thank you.

/Kasper

Debug error 1004
 

Hi again

I have build a little bit on the macro and run into another error. The
Sheets("Original").Selection.ClearContents is not allowed and gives me
another error. Is there any other way to clear the selection before i
copy my new data?

Private Sub button2_Click()

Sheets("Original").Select
Sheets("Original").Range("A9").Select
Sheets("Original").Range(Selection, Selection.End
(xlToRight)).Select
Sheets("Original").Range(Selection, Selection.End(xlDown)).Select
Sheets("Original").Selection.ClearContents

Sheets("Front").Select
Range("B18").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Original").Select
Sheets("Original").Range("A9").Select

ActiveSheet.Paste
Application.CutCopyMode = False

End Sub


/Kasper

Debug error 1004
 

I would drop the .selects and Selection completely:

Option Explicit
Private Sub button2_Click()

Dim LastRow As Long
Dim LastCol As Long
Dim StartCell As Range
Dim RngToCopy As Range

With Worksheets("Original")
Set StartCell = .Range("A9")
LastRow = StartCell.End(xlDown).Row
LastCol = StartCell.End(xlToRight).Column
.Range(StartCell, .Cells(LastRow, LastCol)).ClearContents
End With

With Worksheets("Front")
Set StartCell = .Range("b18")
LastRow = StartCell.End(xlDown).Row
LastCol = StartCell.End(xlToRight).Column
.Range(StartCell, .Cells(LastRow, LastCol)).ClearContents
End With

With Me 'sheet with the commandbutton
Set StartCell = ActiveCell 'I'd use the address if it didn't change
LastRow = StartCell.End(xlDown).Row
LastCol = StartCell.End(xlToRight).Column
Set RngToCopy = .Range(StartCell, .Cells(LastRow, LastCol))
End With

RngToCopy.Copy _
Destination:=Worksheets("Original").Range("a9")

Application.CutCopyMode = False

End Sub

(Untested, but it did compile.)

Kasper wrote:

Hi again

I have build a little bit on the macro and run into another error. The
Sheets("Original").Selection.ClearContents is not allowed and gives me
another error. Is there any other way to clear the selection before i
copy my new data?

Private Sub button2_Click()

Sheets("Original").Select
Sheets("Original").Range("A9").Select
Sheets("Original").Range(Selection, Selection.End
(xlToRight)).Select
Sheets("Original").Range(Selection, Selection.End(xlDown)).Select
Sheets("Original").Selection.ClearContents

Sheets("Front").Select
Range("B18").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Original").Select
Sheets("Original").Range("A9").Select

ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

/Kasper

--

Dave Peterson

Debug error 1004
 

"Selection" is a property of the Application or a specified Window.
So you need...
Selection.ClearContents.

You can make your programming more efficient by not selecting
objects before applying code to them...
'--
With Sheets("Original")
.Range("A9", .Range("A9").End(xlToRight).End(xlDown)).ClearCont ents
End With
--
Jim Cone
Portland, Oregon USA



"Kasper"

wrote in message
Hi again
I have build a little bit on the macro and run into another error. The
Sheets("Original").Selection.ClearContents is not allowed and gives me
another error. Is there any other way to clear the selection before i
copy my new data?

Private Sub button2_Click()
Sheets("Original").Select
Sheets("Original").Range("A9").Select
Sheets("Original").Range(Selection, Selection.End
(xlToRight)).Select
Sheets("Original").Range(Selection, Selection.End(xlDown)).Select
Sheets("Original").Selection.ClearContents

Sheets("Front").Select
Range("B18").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Original").Select
Sheets("Original").Range("A9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

/Kasper

Debug error 1004
 

@Dave: That looks nice... But I think i'm opting for Jims solution as
this requires less rewriting and work. If I have the time for it I
will try your code. Thanks.

@Jim: That did the trick, thank you!!!