Hey guys.

had an old account but couldn't post and couldn't find the activaition email as it was sent a good 140 days ago, so new account and new question.

Currently working on an excel sheet to open and extract data from various files with several folders and then compose it onto a 'main' sheet. Having some trouble though working out how to set the VBA to open and extract specific data. I've managed to get it set-up to check that the 2 folders and the file requested exist (based on criteria in Columns B1, C1 and D1) and at its very basic I have 'recorded' what I need it to do with the file in order to extract the data I want.

'Checking if a folder in file exists
Sub sbCheckingIfAFolderExists()

Dim FSO
Dim sFolder As String
Dim FilePath As String
Dim TestStr As String

'setting the search range for the Cells
Vshop = Range("B1").Value
Vmonth = Range("C1").Value
Vfile = Range("D1").Value

'sending the macro to check the hodling folder for file names whilst setting the search values
sFolder = "W:\Simon Docs\Shop Reports\" & Vshop & "\" & Vmonth
Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(sFolder) Then
MsgBox "Specified Folder Is Available", vbInformation, "Exists!"
Else
MsgBox folder & "Specified Folder Not Found", vbInformation, "Not Found!"
End If
    
    FilePath = "W:\Simon Docs\Shop Reports\" & Vshop & "\" & Vmonth & "\" & Vfile

    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        MsgBox "File doesn't exist"
    Else
        MsgBox "File exist"
    End If

Workbooks.Open Filename:= _
        "W:\Simon Docs\Shop Reports\" & Vshop & "\" & Vmonth & "\" & Vfile
    Selection.End(xlDown).Select
    Range("C409").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-407]C:R[-1]C)"
    Range("C409").Select
    Selection.AutoFill Destination:=Range("C409:D409"), Type:=xlFillDefault
    Range("C409:D409").Select
    Selection.Copy
    Windows("WestonFavellMonthly.xlsx").Activate
    Range("B10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub
The part I've highlighted is what I'm struggling with. I want the macro to sum up all active cells in columns C and D, obviously with the above its doing it to a range, I don't want it to stop on 409 each time as its possible there will be a lot more figures in other sheets that go beyond this.

is there also a way to have a criteria set where, much like my "FilePath = "W:\Simon Docs\Shop Reports\" & Vshop & "\" & Vmonth & "\" & Vfile" Vfile defines where the macro puts the data. In essence a formula.

If Vfile = abc then Windows("WestonFavellMonthly.xlsx").Activate Range("B10").Select or Range("B15")/Range("B20") etc. The active cell using Cfile to determine where it pastes my summed figure?

any help would be appreciated or any further clarification needed I'll happily provide.

Thanks