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
– Ionut, Commented Mar 22, 2018 at 15:36
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
– BruceWayne, Commented Mar 22, 2018 at 15:38
@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
– Jackie, Commented Mar 27, 2018 at 13:17
@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
– Jackie, Commented Mar 27, 2018 at 13:17

warner_sc · Accepted Answer · 2018-03-22 15:49:59Z

1

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

edited Mar 22, 2018 at 15:49

answered Mar 22, 2018 at 15:41

warner_sc

8486 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jackie Over a year ago

I've tried that actually - hits me with a 'subscript out of range' error on that line.

Collectives™ on Stack Overflow

Copy Data from One Excel Workbook to Another Using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related