Need an assist with existing code

[Guest]

Below is code I have attempted to put together to paste data from one
workbook into another. The problem is I need to paste special instead of
just pasting. I have attempted to correct this issue but to no success as I
am admittedly a VBA amatuer. Any help is appreciated.

Sub McoSave()
Application.ScreenUpdating = False

Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim C1 As String
Dim LastRow As Long

Set FromSheet = ThisWorkbook.Worksheets("results")
C1 = "A2:K2"

FromSheet.Range(C1).Copy
Workbooks.Open Filename:="F:\Jeff_H\Survey Test\Survey2.xls"
Set ToSheet = Worksheets("Survey2")

LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1

ToSheet.Paste Destination:=ToSheet.Range("A" & LastRow)
Application.Workbooks("Survey2").Close SaveChanges:=True

Application.ScreenUpdating = True
Beep
strMB = MsgBox("Survey has been saved. " & _
"Thank you for participating.", vbOKOnly, "Finance Group Survey")

Application.ActiveWorkbook.Close SaveChanges:=False

End Sub

 

[Patrick Molloy]

Sub McoSave()

Application.ScreenUpdating = False
DIM WBto As Workbook
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim C1 As String
Dim LastRow As Long

Set FromSheet = ThisWorkbook.Worksheets("results")
C1 = "A2:K2"

SET WBto = Workbooks.Open( Filename:="F:\Jeff_H\Survey
Test\Survey2.xls")
Set ToSheet = WBto .Worksheets("Survey2")

LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1

FromSheet.Range(C1).Copy

ToSheet.Range("A" & LastRow).PasteSpecial xlAll
WBto.Close SaveChanges:=True
Set WBto = Nothing

 

[Guest]

Without looking to deeply at the solution you have provided patrick the one
thing you missed is 6536 should be 65536 in the line

LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1

This would only cause a problem if there were more than 6536 rows of data so
probably not a big deal... but it should be fixed...

HTH