Johnpants
11-10-2005, 07:23 AM
Ok, I have been playing and this is what I have come up with.
I want to create a macro that copies contents of cell "H3" on worksheet "Calendar", then paste it to next empty cell in column "M" on "Data" worksheet, then copy "H4" and paste it to next empty cell in column "N" etc. etc.
This is what I have come up with so far but it is falling over when it starts to run the second part (marked in red writing)
Sub copy6()
Sheets("Calendar").Range("H3").Select
Selection.Copy
Sheets("Data").Select
Range("M65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Calendar").Range("H4").Select
Selection.Copy
Sheets("Data").Select
Range("N65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Calendar").Range("H5").Select
Selection.Copy
Sheets("Data").Select
Range("O65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
The error it brings up is: 'Run-Time error 1004:Select Method of Range Class Failed'
Do I need to add something in here or do I need to go about it a completely diffeent way?
Any help is much appreciated.
Thanks,
John.
I want to create a macro that copies contents of cell "H3" on worksheet "Calendar", then paste it to next empty cell in column "M" on "Data" worksheet, then copy "H4" and paste it to next empty cell in column "N" etc. etc.
This is what I have come up with so far but it is falling over when it starts to run the second part (marked in red writing)
Sub copy6()
Sheets("Calendar").Range("H3").Select
Selection.Copy
Sheets("Data").Select
Range("M65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Calendar").Range("H4").Select
Selection.Copy
Sheets("Data").Select
Range("N65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Calendar").Range("H5").Select
Selection.Copy
Sheets("Data").Select
Range("O65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
The error it brings up is: 'Run-Time error 1004:Select Method of Range Class Failed'
Do I need to add something in here or do I need to go about it a completely diffeent way?
Any help is much appreciated.
Thanks,
John.