Copy Data from One Excel Workbook to Another Using VBA

Question

I'm trying to copy specific data from one excel workbook and place it in another workbook that I've created. I've tried many variations of the following code but none have worked, I keep getting a "Run-time error '1004': Application-defined or object-defined error'" on that line. The line I keep having trouble on is "wb.Sheets("Sheet1").Range("A6").Value = ThisWorkbook.Sheets("Sheet1").Range(c.Offset(0, 8)).Value" - I've included my full code for context.

Private Sub Go_Click()
With Worksheets(1).Range("A:A")
'Search from user input for the entry with that last name
Set c = .Find(LNE.Text, , xlValues, xlWhole)
  If Not c Is Nothing Then
       Dim wb As Workbook
       Set wb = Workbooks.Add("\Documents\Custom Office Templates\KFS_Template.xltm")

       StartDate = c.Offset(0, 3)
       EndDate = c.Offset(0, 4)
       If DateDiff("d", SDE.Text, StartDate) > -1 Then
            If DateDiff("d", EndDate, EDE.Text) > -1 Then
                    Set q = Range("A1")
                    wb.Sheets("Sheet1").Range("A6").Value = ThisWorkbook.Sheets("Sheet1").Range(c.Offset(0, 8)).Value
            End If
       End If
   End If
End With
End Sub

You should use array's for copy large data, it's faster and safer. Also tell me if you know to use array's, if not I can provide you some code — Ionut
I'm going to guess since you did With Worksheets(1).Range("A:A") without noting the workbook, it's throwing an error. Try doing With ThisWorkbook.Worksheets(1)... and see if that does it. — BruceWayne
@lonut I've never used arrays before, could you possibly provide me with an example? I've been trying to teach myself VBA for a few weeks. — Jackie
@BruceWayne the error isn't on that line but thank you anyways! I'm going to try to change that for a cleaner code. — Jackie

warner_sc warner_sc · Accepted Answer · 2018-03-22T15:41:21

Try setting C as range, I think the problem is because you're calling a range object using a range object already.

Private Sub Go_Click()
Dim c As Range
With Worksheets(1).Range("A:A")
'Search from user input for the entry with that last name
Set c = .Find(LNE.Text, , xlValues, xlWhole)
  If Not c Is Nothing Then
       Dim wb As Workbook
       Set wb = Workbooks.Add("\Documents\Custom Office Templates\KFS_Template.xltm")

       StartDate = c.Offset(0, 3)
       EndDate = c.Offset(0, 4)
       If DateDiff("d", SDE.Text, StartDate) > -1 Then
            If DateDiff("d", EndDate, EDE.Text) > -1 Then
                    Set q = Range("A1")
                    wb.Sheets("Sheet1").Range("A6").Value = c.Offset(0, 8).Value
            End If
       End If
   End If
End With

End Sub

Copy Data from One Excel Workbook to Another Using VBA

1 Answers