CODEWITHSUNDEEP
vbaexcel
Jackie
Jackie

Reputation: 145

Copy Data from One Excel Workbook to Another Using VBA

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

Upvotes: 0

Views: 387

Answers (1)

warner_sc
warner_sc

Reputation: 848

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

Upvotes: 1

Related Questions