Transfer of Data in Specific Rows in Specific Worksheet

Question

Need your help and expertise on my problem.

I have a report with vba code that enables it to pull the reports based on the path inputted in "List" sheet. However, it does not read in which exact cell it should be inputted based on the location I dictated.

Expected: The data that will be inputted in "MasterData" sheet should start at Row 1 since the report has its own header.

The data that will be inputted in "2ndsheet" and "3rdsheet" should start at Row 2 since I will be the one to create the header. This is already correct as it starts at Row 2.

In my my file, "List" sheet, there is the cell location of the reports where to paste it but even if I locate it to the expected cells, the code is not following it.

Below is the vba:

Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String

Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet 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) & ":" & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

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

    Range(strCopyRange).Select
    Selection.Copy

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

    Selection.PasteSpecial xlPasteAll, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close False
    Sheets("List").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

Public Function LastRowInOneColumn(col)
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
End Function

Please see attached screenshots:

List sheet:

MasterData sheet:

2ndsheet - correct row:

Thanks.

Please read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to your code. Using `.Select` and `.Activate` can cause many issues, so you should get rid of this first. Also specify a worksheet for every `Range()` and `Cells()` etc. like `Worksheets("Sheet1").Cells(1, 1)`. — Pᴇʜ, Jul 09 '18 at 07:38

Transfer of Data in Specific Rows in Specific Worksheet

0 Answers0