Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Debug Error | Excel Discussion (Misc queries) | |||
Macro Debug Error | Excel Discussion (Misc queries) | |||
Debug Error in Code | Excel Discussion (Misc queries) | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |