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
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
RE: Excel VBA Runtime error 1004
Fab, thanks Marius. I'll give that a go this week.
Thanks
Gem.
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
