Excel VBA Runtime error 1004

Resolved · High Priority · Version 2010

Tue 23 Dec 2014, 09:45 Delegate Gemma said...

Gemma has attended:
Excel VBA Intermediate course

Excel VBA Runtime error 1004

Hi, I have a macro which Doug helped me set up, which basically extracts raw data from 6 sheets, combines then into one raw data sheet, and then separates them back out by location.

When I try and run it on my latest monthly report, i get a runtime error 1004, which says the information can't be pasted because the Copy area and the paste area aren't the same size and shape.

I can't see where there is a difference on my latest spreadsheet versus my new one. Can anyone help? I've put the code as below. It's the ActiveSheet.Paste at the bottom that the debugger higlights.

My other question is, if I have a raw data sheet with no data on it, only headings, will this mess up the macro? Is there a line of code I can put in to allow for this?

Many thanks in advance for any help.

Gem.

Sub CreateRawdataTotal()
'Copy data from Raw Data 1 to Total Raw Data including headings
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Total Raw Data"
Sheets("Total Raw Data").Select
Sheets("Raw Data 1").Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheets("Total Raw Data").Select
Range("A1").Select
ActiveSheet.Paste

'Copy data from Raw Data 2 to Total Raw Data
Sheets("Raw Data 2").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Total Raw Data").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

Mon 5 Jan 2015, 11:41 Trainer Marius said...

RE: Excel VBA Runtime error 1004

Hi Gemma,

Below is a variation of your code which includes a line of code which deletes entire rows if it finds empty cells in column A, an If Statement to handle empty cells below the headers and an alternative line for finding the last row of data.

Sheets("Raw Data 2").Select
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A2").Select
If ActiveCell = "" Then
Exit Sub
Else
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy Destination:=Sheets("Total Raw Data").Range("A" & Range("A1048576").End(xlUp).Row + 1)
'The line above is an alternative to find the last line of pasted data if your code below breaks.
End If
'Sheets("Total Raw Data").Select
'Range("A1").Select
'Selection.End(xlDown).Select
'ActiveCell.Offset(1, 0).Range("A1").Select
'ActiveSheet.Paste

I hope this helps!

Kind regards
Marius Barnard
Excel Trainer

Mon 5 Jan 2015, 14:07 Delegate Gemma said...

RE: Excel VBA Runtime error 1004

Fab, thanks Marius. I'll give that a go this week.

Thanks

Gem.

Mon 16 Feb 2015, 16:10 Delegate Gemma said...

RE: Excel VBA Runtime error 1004

Hi Marius/Doug

So, i've been having a play with this, but still hitting problems.

I have changed the code as per what I think you meant above, so that it now reads as below pasted below - but I might have misunderstood???!!!!

I now get a runtime error 1004 that says no cells found for the 2nd line we added (Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete).

So, that's my new problem. The old one is still the same, that I get a runtime error where it can't copy and paste into the same area (on the Total Raw Data sheet) as it says the sizing is different. This always happens on the 3rd raw data set where there is usually no data, but it's presumably to do with the pasting?

Any help much appreciated. I sent Doug the spreadsheet last time, but not sure that is workable!! ?

Thanks

Gem.

'Copy data from Raw Data 3 to Total Raw Data
Sheets("Raw Data 3").Select
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A2").Select
If ActiveCell = "" Then
Exit Sub
Else
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy Destination:=Sheets("Total Raw Data").Range("A" & Range("A1048576").End(xlUp).Row + 1)
End If
Sheets("Total Raw Data").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Quickly hide and unhide rows and columns

Use the keyboard shortcut Ctrl+9 to hide selected rows and Ctrl+0 to hide selected columns. The good thing about this shortcut is that you do not need to select entire rows or columns. For example, select B3:D3 then press Ctrl+0 to hide columns B to D.

Ctrl+Shift+9 unhides rows and Ctrl+Shift+0 unhides columns.

View all Excel hints and tips