I am trying to Copy data from one document to another. I have a userform that allows for you to select which sheet you want to copy from and to. I also have to where it will paste into the next empty cell in the line. I have tried paste to a single cell reference and i have tried selecting the whole paste area but still getting the same fault.
VBA Code:
Public OpenBook As Workbook 'Workbook opened to copy data from
Private Sub CommandButton1_Click()
'-----Variable Setup----------------------
Dim i As Integer 'For loop count
Dim F As String 'From Location = Sheet name to copy From
Dim T As String 'To Location = Sheet name to copy To
Dim C As String 'Column To paste to
Dim FR As String 'Row count for from data
Dim R As String 'Row to Paste to
'-----Variables---------------------------
C = Left(Sheet2.Cells(3, Sheet2.Range("D4").Value), 1) '=J
'-----For Loop----------------------------
For i = 1 To 1 'For loop set to 1 for testing purposes.
F = Controls("Label" & i).Caption 'Sheet ID to copy data from on another worksheet
T = Controls("ComboBox" & i).Value 'Sheet ID to paste data to on this worksheet
R = Sheets(3).Range(C & Rows.Count).End(xlUp).Row + 1 'Row to paste data to = 2
FR = OpenBook.Sheets(F).Range("D" & Rows.Count).End(xlUp).Row 'Number of cell with data in them to copy = 11
MsgBox C & R & FR ' Used to verify calculations are correct
OpenBook.Sheets(F).Range("D1:F" & FR).Copy
ThisWorkbook.Sheets(T).Range(C & R).Select
Selection.PasteSpecial xlPasteValues
Next i
OpenBook.Close False
Unload Me
End Sub