Solved: Macro Copying and Pasting

[Johnpants]

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.

[Johnpants]

I have figured out where I have gone wrong with this:



Sheets("Calendar").Select
Range("H3").Select
Selection.Copy
Sheets("Data").Select
Range("M65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Sheets("Calendar").Select
Range("H4").Select
Selection.Copy
Sheets("Data").Select
Range("N65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Sheets("Calendar").Select
Range("H5").Select
Selection.Copy
Sheets("Data").Select
Range("O65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste


The problem I have now may need a new post as it is a little off topic, but is it possible to only paste the values of a cell rather than the formula itself?

Thanks,

john.

[mvidas]

Hi John,

It is possible, you could use the .PasteSpecial method (with xlPasteValues), or do it like this:Sheets("Data").Range("M65536").End(xlUp).Offset(1, 0).Value = _
Sheets("Calendar").Range("H3").Value
Sheets("Data").Range("N65536").End(xlUp).Offset(1, 0).Value = _
Sheets("Calendar").Range("H4").Value
Sheets("Data").Range("O65536").End(xlUp).Offset(1, 0).Value = _
Sheets("Calendar").Range("H5").ValueOr, to be even quicker and shorter:Sheets("Data").Range("M65536").End(xlUp).Offset(1, 0).Resize(1, 3).Value = _
Application.Transpose(Sheets("Calendar").Range("H3:H5").Value)Matt

[Johnpants]

Thank you, just what I was looking for!

Much appreciated.