Transfer of Data in Specific Cells for Each worksheet

[Zhel]

Hi Guys,

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 dicatated.

Expected:
The data that will be inputted in "MasterData" sheet should start at Row A
The data that will be inputted in "2ndsheet" and "3rdsheet" should start at Row B.

Attached is my sample file also.

Below is my vba code:

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)
    'Find the last used row in a Column: column A in this example
    'http://www.rondebruin.nl/last.htm
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
End Function

 

[Stevie]

Hi Zhel,
I have not downloaded the file, as it appears you haven't included files which act as a data source, which I believe is the main point of your Macro.
Further, 'Row A' and 'Row B' is quite confusing, as rows are numeric, whereas columns are referred to by letters.
However, if I understand what you are trying to do, the error in your code appears to be in this line:

Cells(lastRow + 1, 1).Select

This is the selection you make to paste data to, and will allways be column 'A' due to the '1' in your cells() selection.
I would suggest changing this to:

range(strStartCellsColName & lastRow + 1).Select

I suggest using range over cells as your 'strStartCellsColName' variable is a string which implies it is the column letter.
If this does not solve your problem, or I have misunderstood what you are trying to achieve, please add some clarification.

If this was helpful, please click 'Like'
^.^

 

[Zhel]

Hi Stevie,

Thank you for your inputs. What I meant is:

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.

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.

I attached the files also.

Thanks.