Get and Transfer files in Specific sheets

Question

I am currently doing a report in which I will get the reports based on the link/path and file name I indicated in a specific cell and data should be transferred in the specific sheet I also indicated. This is needed as the path is always changing depending on where the user saved the files.

1st thing is, I have the "List" tab where I have inputted the file name and full path of each files and in what location should it be pasted. For example, File A should be pasted to "MasterData" sheet. File B, should be in the next tab, File C should be in the other tab.

Then, when I use the vba, it will get my files. File has been copied to "MasterData" sheet but the 2nd and 3rd file were copied under the data of File A which is my problem.

The 2nd file and 3rd files also are in text format but I want the Text Wizard to pop up first so the user can choose if the data should be delimited or Fixed width before copying it to the assigned sheets.

List Sheet

MasterData Sheet Results

I cannot attach my file here :(

This is my current 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("MasterData").Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select

        Selection.PasteSpecial xlPasteValues, 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

Welcome to Stack Overflow! Questions like these are [too broad](https://stackoverflow.com/help/on-topic). Try asking [minimal specific questions about programming issues](https://stackoverflow.com/help/mcve), by splitting your problem into smaller questions. If you require tutorials and courses on how to accomplish your goals, unfortunately Stack Overflow is not the right place to do it. — sɐunıɔןɐqɐp, Jul 04 '18 at 07:10
① Your code seems to be incomplete. There is a `Loop` but no `Do`. ② Read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), this will already prevent issues and makes your code faster and more reliable. ③ Because Stack Overflow is no code writing service the question *"What is the correct code on this?"* is too broad to answer. You must show what errors you get and where, or explain what your code actually does and what you expected instead. So we need to know what exactly in your code doesn't work. — Pᴇʜ, Jul 04 '18 at 07:21
@PEH Hi, thank you for pointing that out. actually, the code is working fine. My problem is when the code reads the file name of the second file I inputted in the master data sheet, instead it goes to the next tab, it will only go under the data of the first file. — user9999402, Jul 04 '18 at 08:21
@user9999402 Your code is still incomplete! Please first update your question. Add the relevant code part (we need to see the complete loop). And by "next tab" you mean a worksheet? Please put all your questions into the question part above and be as detailed as possible and use the correct terms. — Pᴇʜ, Jul 04 '18 at 08:25
@Pᴇʜ, apologies for that. My pc crashed and I thought it is already complete. I already edited this. Would this suffice? I attached also the screenshots. — user9999402, Jul 04 '18 at 08:44

score 0 · Accepted Answer · edited Jul 04 '18 at 09:27

0

I suspect that to solve the pasting issue the line that reads:

Sheets("Master Data").select

should read

Sheets(strWhereToCopy).select

edited Jul 04 '18 at 09:27

Tim Diekmann

7,755
11
41
69

answered Jul 04 '18 at 09:22

Mick Fuller

26
2

I tried, but the error message is showing that it is not complete. – user9999402 Jul 04 '18 at 09:34
That is odd, it is working fine for me. Try commenting out the onerror line so that you will be able to see at which point the code fails. – Mick Fuller Jul 04 '18 at 10:45
Sorry for that. I have mistakenly updated the vba code. It is now working. But one help needed please. for the text files, before it pasted to the necessary sheets, I want first to open the text wizard so the user would be able to format it first. (It is the user option to use delimited or fixed width. – user9999402 Jul 05 '18 at 01:53

Get and Transfer files in Specific sheets

1 Answers1