VBA to pull data from closed excel workbooks to Master within sub folder structures

[JaLoXL]

Hi,

I have been searching for a solution to this issue for quite some time with no luck, some findings get me a portion of the way but not quite what I need. I do not have any VBA experience but I am very competent with formula writing.

I need to pull data ranges from closed, excel sheets stored on a shared drive (F:\STRUCTURES\ACCOUNTS\Cost Tracking\BUDGETS\COSTINGS\EMPLOYEE ANALYSIS), these documents are stored firstly via Month January/February/March etc then Week Commencing 01.01.19/08.01.19/15.01.19. Within each of the Week Commencing folders there are a number of Spreadhseets but I need to look inside only one - 'Labour Test File'. Within this workbook I need to extract the range B3:AA42 from the worksheet named 'Analysis - Costs'.

When this data has been found I would want this to paste into a document stored on the same file path named 'Master', using the next available to row to paste the next week's data.

Your help would be greatly appreciated on this as I have been struggling to find a suitable solution for this for some time.

 

[Yongle]

Ok
- looks like we are nearly there
- the correct data is being pulled and placed where required

Answers to my earlier questions is now all I should need to finalise everything

 

[JaLoXL]

Ok
- looks like we are nearly there
- the correct data is being pulled and placed where required

Answers to my earlier questions is now all I should need to finalise everything

I will review the outstanding questions now.

Just a quick reference for the final code could you alter slightly to paste special when copied. This is due to the fact that the data we are copying are formulas. When I closed and reopened the Master #VALUE ! appear in all the cells.

 

[JaLoXL]

What I know
Master.xlsm is in same folder as Labour Test File.xlsx. VBA resides in Master. Values go in next available row in "Sheet 1"

What I need
Q1 Does data go in columns A:Z (or B:AA) ?
Q2 How does VBA get told which is the next week to pull ?
- user option ? \ does previous data tell us ?\ get VBA to update a cell in Master each time
Q3 Did the formula work ? (see post#11 & post#5)

Q2. As there are files that aren't yet created (Month & WC) I am quite happy to use the prompt function currently available with your code to dictate where the Master should be looking to extract the data next.

 

[Yongle]

- a couple of lines inserted for smoother performance
- paste special introduced as requested
- copy range now starts from row 5

Test and let me know if anything requires correction or modification

Sub GetValues()
    [COLOR=#ff0000]Application.ScreenUpdating = False[/COLOR]
    Dim fMonth As String, fWkComm As String, varPath As String, s As String, fullPath As String
    Dim ws As Worksheet, wb As Workbook, nextRow As Long
    Const fPath = "F:\STRUCTURES\ACCOUNTS\Cost Tracking\BUDGETS\COSTINGS\EMPLOYEE ANALYSIS"
    Const fName = "Labour Test File.xlsx"
    Const fSheet = "Analysis - Costs"
    Const fRng = "[COLOR=#ff0000]B5[/COLOR]:AA42"
    Const pSheet = "Sheet1"
    Const pColumn = "A"
[I][COLOR=#006400]'variable elements[/COLOR][/I]
    fMonth = "January 2019"
    fWkComm = "WC 08.01.19"
    fMonth = InputBox("Enter month", "MONTH ?", fMonth)
    fWkComm = InputBox("Enter week", "WEEK ?", fWkComm)
    s = Application.PathSeparator
[COLOR=#006400][I]'full path string[/I][/COLOR]
    fullPath = fPath & s & fMonth & s & fWkComm & s & fName
[COLOR=#006400][I]'does file exists?[/I][/COLOR]
    If Dir(fullPath) > "" Then
        Set wb = Workbooks.Open(fullPath)
    Else
        MsgBox "File not found"
        Exit Sub
    End If
[COLOR=#006400][I]'get values[/I][/COLOR]
    Set ws = ThisWorkbook.Sheets(pSheet)
    nextRow = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
    wb.Sheets(fSheet).Range(fRng).Copy
    With ws.Cells(nextRow, pColumn)
        .PasteSpecial ([COLOR=#ff0000]xlPasteValues[/COLOR])
        .PasteSpecial ([COLOR=#ff0000]xlPasteFormats[/COLOR])
            Application.DisplayAlerts = False                   [I][COLOR=#006400]'suppress large clipboard messag[/COLOR][/I][COLOR=#006400]e[/COLOR]
                wb.Close False
            Application.DisplayAlerts = True
        .Select
    End With
End Sub