0

I am trying to insert all rows from one excel doc called "Coding", which is 71 rows, into another excel doc named "Master", then deleting all of the matching old data (matching rows that are already in the master doc) using VBA. So far I have had success with the deleting duplicates portion, but I cannot get the data to import. This is my current code for the import:

Sub GetData()
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim strFile As String
    With Application.FileDialog(1) ' msoFileDialogOpen
        .Filters.Clear
        .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
        .InitialFileName = "C:\documents\Master"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            MsgBox "No file selected", vbCritical
            Exit Sub
        End If
        End With
        With Sheets("Master")
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
    Application.ScreenUpdating = False
    Set wshT = ActiveSheet
    Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
    Set wshS = wbkS.Worksheets(1)
    wshT.Range("A" & lastRow).Value = wshS.Range("Number").Value
    wshT.Range("C" & lastRow).Value = wshS.Range("Injury").Value
    wshT.Range("D" & lastRow).Value = wshS.Range("PA").Value
    wshT.Range("E" & lastRow).Value = wshS.Range("PA2").Value
    ' Optional: close source workbook
    wbkS.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

I am excluding column B from "Coding" intentionally. The doc "Master" already has 19913 rows, so I will need to insert the information into row 19914. I am getting the error:

Run-time error "1004":
Method "Range" of object "_Worksheet" failed

I am very new to VBA so it may just be a small error I'm not noticing. Any suggestions are welcome, thanks in advance!

BigBen
  • 46,229
  • 7
  • 24
  • 40
MissMay
  • 1
  • 1
  • There's no range `"A"` or `"C"` - those are not valid range references. You probably want to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and then use it, e.g. `.Range("A" & lastRow).Value = ...` – BigBen May 24 '21 at 18:15
  • There's a link in my comment (you may have to refresh the page). – BigBen May 24 '21 at 18:19
  • @BigBen I still doesn't seen to be working. It's still giving me the same error. I understand the last row item and have added that into my coding. I'm not entirely sure if I put it in the correct spot, though. – MissMay May 24 '21 at 18:35
  • Please [edit] your question with your revised attempt. – BigBen May 24 '21 at 18:40
  • Seems like the issue then is with the named ranges, "Number", "Injury", etc. Also you probably want: `lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1` to avoid overwriting data. – BigBen May 24 '21 at 18:49

0 Answers0