Open, Copy data and Close a workbook based on the names of the tabs in my active workbook

[Aurore87]

Hello everyone,
Apologies English is not my first language and I am very new to writing VBA codes. So far, I have only copied and adapted parts of the codes to fit my needs.

Recently, I have created a workbook, each tab has the name of a treatment site. Daily, I receive data about these treatment sites in separate workbooks.
Active workbook = Workbook that I have created
Daily workbooks = Workbooks I receive daily
Folder path = Active workbook, Dashboard tab, Cell b3
What I want my macro to do is: -
1- for each tab of my "active workbook", find in my folder the "daily workbook" that has the same name as the tab in my "active workbook", then
2- open this "daily workbook", copy the selection (the data is already selected), paste it to the corresponding tab (same name) in cell b6 of my "active workbook", close the "daily workbook" (no need to save) and repeat for the following tab of my "active workbook".

I am sure this is very easy but I am struggling a lot with this, especially the first part of the question.
Could anybody help me, please?



Many thanks
Aurore

 

[John_w]

Try this. Assumes the daily workbooks are .xlsx files.

Public Sub Copy_Selection_From_Tab_Workbooks()

    Dim sourceFolder As String
    Dim ws As Worksheet
    Dim sourceWorkbook As Workbook

    Application.ScreenUpdating = False
    
    With ActiveWorkbook
        sourceFolder = .Worksheets("Dashboard").Range("B3").Value
        If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
        For Each ws In .Worksheets
            If StrComp(ws.Name, "Dashboard", vbTextCompare) <> 0 Then
                Set sourceWorkbook = Workbooks.Open(sourceFolder & ws.Name & ".xlsx")
                Selection.Copy ws.Range("B6")
                sourceWorkbook.Close False
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

 

[Aurore87]

Hi @John_w,

Sorry for the late reply, I was on site visits and so away from my laptop.

I just tried it out and it worked "HURRAY"!

Thank you so much for your help!
A.