VBA Runtime error '1004'

[Dean]

I have a macro that selects a range of data on one sheet, selects an other
sheet, finds the next available row and pastes the data in. This has worked
!I've seen it and was happy.

I've started to add another seperate routine to cater for single rows of
data on the original sheet and I'm now getting an error.....

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

Really strange thing is that it's appearing in different places through out
the code (F8 stepping through).

Private Sub CommandButton1_Click()
' *******************************
' ****** Import Bradley *********
' *******************************

Sheets("Import sheet").Range("A7").Select
If IsEmpty(Range("A8").Value) Then Single_Row_Bradley: Exit Sub

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Bradley").Range("A7").Select
Selection.End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Import Sheet").Select
Range("A7").Select
Application.CutCopyMode = False
End Sub

Private Sub Single_Row_Bradley()
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Bradley").Parent.Activate
Sheets("Bradley").Select
Range("A7").Select
Selection.End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Import Sheet").Select
Range("A7").Select
Application.CutCopyMode = False
End Sub

Two sub routines to prety much do the same but one is for a range of data
the second only deals with a single row.

Any and all help would be appreciated.

Thanks

 

[Dean]

Answered!
I needed to"Activate" the sheet, not just select it!!!

 

[Jacob Skaria]

Dear Dean

Replace the below code in Sub Single_Row_Bradley

'Selection.End(xlDown).Offset(1, 0).Select

If Range("A8") <> "" Then Selection.End(xlDown).Offset(1, 0).Select
If Range("A7") <> "" Then Range("A8").Select

If this post helps click Yes

 

[Jacob Skaria]

Dear Dean

You have have a single procedure to handle this. Please see below.

If this post helps click Yes
---------------
Jacob Skaria

Private Sub CommandButton1_Click()
Dim intRowI
Dim intRowB

intRowI = Sheets("Import sheet").Range("A65536").End(xlUp).Row
intRowB = Sheets("Bradley").Range("A65536").End(xlUp).Row

Sheets("Import sheet").Select
Sheets("Import sheet").Rows("7:" & intRowI & "").Select
Selection.Copy
Sheets("Bradley").Select
Sheets("Bradley").Range("A" & intRowB + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Import Sheet").Select
Range("A7").Select
Application.CutCopyMode = False

End Sub

 

[Dean]

Jacob,

I'n not quite sure what good this would do. I think my code may be
confusing you (I'd understand...it does me).

I've already made the descision that the next row is blank in the first sub
and jumped to the "Single_row_Bradley" so I don't need to check again.
Or have I miss understood what your code should do?

I have already sorted the error by "Activating" the sheet and not just
selecting it.

Thanks
Dean

 

[Jacob Skaria]

Dear Dean

You have have a single procedure to handle this. Please see below.

If this post helps click Yes
---------------
Jacob Skaria

Private Sub CommandButton1_Click()
Dim intRowI
Dim intRowB

intRowI = Sheets("Import sheet").Range("A65536").End(xlUp).Row
intRowB = Sheets("Bradley").Range("A65536").End(xlUp).Row

Sheets("Import sheet").Select
Sheets("Import sheet").Rows("7:" & intRowI & "").Select
Selection.Copy
Sheets("Bradley").Select
Sheets("Bradley").Range("A" & intRowB + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Import Sheet").Select
Range("A7").Select
Application.CutCopyMode = False

End Sub

 

[Dean]

Jacob,

I new it would be me missunderstanding. Looks brill. I'll give it a go.
Thanks
Dean