Macro errors if it has nothing to copy and paste into sheet 1

DJMCCOURT

New Member
Joined
Jul 4, 2019
Messages
14
Hi, my first post so be gentle with me!

I have a macro that copy and pastes data from sheet 3 after i have an external system report populate sheet 3 with data. There are 4 reports that run in a chain and if one doesnt find any data to export to sheet 3 the macro throws up a debug error stopping my report from running

So what i need to add to this macro is a type of error do message to continue and not error if it cant find anything to copy and paste.

The Macro is...
Sub APPENDDATA()


ActiveWorkbook.Sheets("Data").Select

Dim N As Long
N = Cells(1, 1).End(xlDown).Row
Set DT = Range("A1:Y" & N)
DT.Select
Selection.Copy


ActiveWorkbook.Sheets("BSR").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone


ActiveWorkbook.Sheets("Data").Select
DT.Select
Selection.ClearContents





End Sub




The error i get is a runtime error 1004
"we cant paste because the copy area and paste area arent the same size.

It only gives this error if there is no data to paste.


Can anyone assist??

Cheers
Dave
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi & welcome to MrExcel.
Change this
Code:
N = Cells(1, 1).End(xlDown).Row
to
Code:
N = Cells(1, Rows.Count).End(xlup).Row
 
Upvote 0
Hi & welcome to MrExcel.
Change this
Code:
N = Cells(1, 1).End(xlDown).Row
to
Code:
N = Cells(1, Rows.Count).End(xlup).Row


Hi, sorry just tried this but it gave me another error

Application-defined or object-defined error - when i click debug it shows that line of code in yellow...

Cheers
Dave
 
Upvote 0
Maybe:
Code:
Sub CopyRange()
    Dim scrWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Data")
    Set desWS = Sheets("BSR")
    With srcWS
        If .Range("A1") <> "" Then
            .Range("A1", .Range("Y" & .Rows.Count).End(xlUp)).Copy
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            .Range("A1", .Range("Y" & .Rows.Count).End(xlUp)).ClearContents
            Application.CutCopyMode = False
        End If
    End With
End Sub
 
Upvote 0
Apologies it should be
Code:
N = Cells(Rows.Count, 1).End(xlUp).Row
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top