0

I'm using a table to pull information from separate work books.currently the table has a set end cell, but I'm pulling into too much information. I want to set the end cell to the last row of the data in column D. I need help modifying the code to set the end cell to a dynamic range.

I've already tried to use lastRow = .Cells(.Rows.Count, col).End(xlUp).Row but I keep getting

compile error

at the preceding .Offset that is invalid or unqualified reference

Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String
    Dim strCopySheet As String

    strListSheet = "List"

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select

'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""

        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        strCopyRange = ActiveCell.Offset(0, 2) & ":" & lastRow = 
.Cells(.Rows.Count, col).End(xlUp).Row
        strWhereToCopy = ActiveCell.Offset(0, 4).Value
        strCopySheet = ActiveCell.Offset(0, 6).Value
        strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

        Application.Workbooks.Open strFileName, UpdateLinks:=False, 
ReadOnly:=True
        Set dataWB = ActiveWorkbook

        Sheets(strCopySheet).Select
        Range(strCopyRange).Select
        Selection.Copy

        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select

        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
        dataWB.Close False
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not 
complete."
    Exit Sub

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
JCornin
  • 5
  • 2
  • 2
    Please read up on how to [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – SJR Jan 29 '19 at 18:01
  • I Agree with SJR, you should avoid using the select. In regards to you defining 'lastrow.' I don't see too much in the way there, but again I would work select – IrwinAllen13 Jan 29 '19 at 18:43

0 Answers0