Help ..Code for copy data in to master workbook in different worksheets

[Dinesh_Excel]

Hello All,

I have to copy data from 6+ workbooks and paste it into a master workbook in different worksheets .

All the workbooks are located in a folder on my desktop: C:\Users\xbv\Desktop\TestFolder

Request your help here please.



output :

 

[vletm]

Dinesh_Excel
Is this 'once-in-the-lifetime' or daily action?
Is there always only one sheet in every workbook?
Have You tested to do that copy manually?

 

[Dinesh_Excel]

Hi Vletm,

Thanks for your reply,
1.this is a lifetime activity which is my bread and butter.
2.yes there is always a single worksheet in all 5 workbooks but the range is different.
3.this data is picked up by the client from the C:\temp where we run a dashboard on it by running some formulas.

I tried running this code already in the forum but could not succeed.

Would appreciate your help on the same.

Thank you again.

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:=True
        Set dataWB = ActiveWorkbook
       
        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

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

 

[vletm]

Dinesh_Excel
1) Is this 'once-in-the-lifetime' or daily action? Which one?

2) Is range always same with each file?

3) Have You tested to do that copy manually?
... about ~five steps, if five files
... Are those fixed (two) files?

4) Maybe interesting code
... but if it won't work for You, then it's not useful for You.

>> before something could do, need clear rules <<
This has already changed from original case.
Ideas?

 

[Dinesh_Excel]

Hi Vletm


1) Is this 'once-in-the-lifetime' or daily action? Daily action

2) Is range always same with each file? no, the range is not constant across all workbooks

3) Have You tested to do that copy manually?
... about ~five steps, if five files -->>> Maximum the files can be 7 and minimum would be 5
... Are those fixed (two) files?

4) Maybe interesting code
... but if it won't work for You, then it's not useful for You - My bad it was my mistake

>> before something could do, need clear rules <<