Copy Paste From Workbook to Another Workbook

[reberryjr]

Ok, I'm trying to copy a row from one workbook, and paste it to another one. For some reason, I'm getting an error at the code in red font. I'm not sure why, as I'm activating the page I want to paste the data to. I've verified that the data is going onto the clipboard, it's just not pasting. Where am I going wrong?

Private Sub cmd_Submit_Click()

Dim ws2, ws3, ws4 As Worksheet
Dim CS As Workbook
Dim CSPath As String
Dim CSFName As String


Set ws2 = ThisWorkbook.Sheets("Summaries")
Set ws3 = ThisWorkbook.Sheets("Bios")
Set ws4 = ThisWorkbook.Sheets("Pymt Tracker")
LastRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row
LastRow3 = ws3.Range("E" & Rows.Count).End(xlUp).Row
LastRow4 = ws4.Range("C" & Rows.Count).End(xlUp).Row


Set CS = Workbooks.Open("C:\Users\Rodger\Desktop\Bodies by Trish\CS_Template.xlsm")
CSPath = "C:\Users\Rodger\Desktop\Bodies by Trish\"
CSFName = Me.txt_ClientID


CS.Activate
CS.Sheets("Bio").Range("A2:Z2").Copy
ThisWorkbook.Activate
[COLOR=#ff0000]ws3.Range("A" & LastRow3 + 1).Paste[/COLOR]
Application.CutCopyMode = False
CS.Activate
ActiveWorkbook.SaveAs Filename:=CSPath & CSFName, FileFormat:=52
CS.Close


End If


MsgBox "The new Client's data has been recorded."


End Sub

 

[offthelip]

CS.Activate
CS.Sheets("Bio").Range("A2:Z2").Copy
ThisWorkbook.Activate

This code Activate workbook CS_Template.xlsm (Cs) then copies a range to the clipboard, then it Activates "Thisworkbook" which at this point is workbook Cs_Template.xlsm. NOT the workbook where you defined WS3.
the way round this is to put this code just after the dim statements:

 ThisworkbookName = ActiveWorkbook.Name

then instead of the thisworkbook.activate put:

Windows(ThisWorkbookName).Activate

 

[reberryjr]

That makes sense. I implemented that code, but I'm getting the same error. I thought maybe it was because the form was still open, so I added a line to close it before the copy and paste action is hit, but I'm still getting the same error.

Private Sub cmd_Submit_Click()
Application.ScreenUpdating = False
Dim ws2, ws3, ws4 As Worksheet
Dim CS As Workbook
Dim CSPath As String
Dim CSFName As String
ThisWorkbookName = ActiveWorkbook.Name
Set ws2 = ThisWorkbook.Sheets("Summaries")
Set ws3 = ThisWorkbook.Sheets("Bios")
Set ws4 = ThisWorkbook.Sheets("Pymt Tracker")
LastRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row
LastRow3 = ws3.Range("E" & Rows.Count).End(xlUp).Row
LastRow4 = ws4.Range("C" & Rows.Count).End(xlUp).Row
'Set CS = Workbooks.Open("C:\Users\Rodger\Desktop\Bodies by Trish\CS_Template.xlsm")
'CSPath = "C:\Users\Rodger\Desktop\Bodies by Trish\"
Set CS = Workbooks.Open("C:\Users\Rodger\Desktop\Bodies by Trish\CS_Template.xlsm")
CSPath = "C:\Users\Rodger\Desktop\Bodies by Trish\"
CSFName = Me.txt_ClientID
 
Unload Me
CS.Activate
CS.Sheets("Bio").Range("A2:Z2").Copy
Windows(ThisWorkbookName).Activate
ws3.Range("A" & LastRow3 + 1).Paste
Application.CutCopyMode = False
CS.Activate
ActiveWorkbook.SaveAs Filename:=CSPath & CSFName, FileFormat:=52
CS.Close
End If
MsgBox "The new Client's data has been recorded."
Application.ScreenUpdating = True
End Sub

 

[offthelip]

change this line:

[COLOR=#333333]ws3.Range("A" & LastRow3 + 1).Paste[/COLOR]

to

[COLOR=#333333]ws3.Range("A" & LastRow3 + 1).PasteSpecial[/COLOR]

 

[reberryjr]

That did it. I can't tell you how many variations of "stuff" I tried. To think it was just one word. Almost as bad as a , . " or ).

Thank you!!!