Copy a first sheet of a workbook in the first sheets of other workbooks

try this code

Sub copySheet1ToAllWbInFolder()
    Dim FileName, ws As Worksheet, destWB As Workbook
    Dim pPath As String
    pPath = "D:\DATI\prova"
    Set Sh1 = ActiveWorkbook.Sheets(1)
    With CreateObject("scripting.filesystemobject")
       For Each FileName In .getfolder(pPath).Files
          With Workbooks.Open(FileName)
             Sh1.Cells.Copy .Worksheets.Add(after:=.Sheets(.Sheets.Count)).Range("A1")
'            ActiveSheet.Name = "???" 'Name sheet
            .Close True
          End With
        Next
    End With
End Sub

Hi khracking,

unfortunately you did not write how the macro should know which files need to be opened. I have therefore assumes there is a separate worksheet (called "Files") which has the path (ending with a \) in column A and the filename in column B.

Option Explicit

Sub Copy_data_to_workbooks()
    Dim i As Integer
    Dim lastrow As Integer
    Dim xw As Workbook
    Dim xs As Worksheet

    lastrow = tbl_files.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    For i = 2 To lastrow    'loop through all rows on file table starting in row 2
        On Error Resume Next
        Set xw = Worksheets("Files").Cells(i, 2).Value    'filename of targetfile taken from Files Table
        If Not xw Is Nothing Then    'Check if targetfile is already open
            On Error GoTo 0
            xw.Activate
        Else    'if not open then open file
            On Error GoTo 0
            Workbooks.Open Worksheets("Files").Cells(i, 1).Value & Worksheets("Files").Cells(i, 2).Value
            Set xw = ActiveWorkbook
        End If

        xw.Sheets(1).Activate
        Set xs = ActiveSheet
        ThisWorkbook.Sheets(1).Cells.Copy Destination:=xs.Range("A1")    'copy all contents of sheet1 to targetfile
        xw.Close True    'close targetfile and save
        Set xw = Nothing
    Next i
    Set xw = Nothing
    Set xs = Nothing
End Sub

Please let me (and the other forum members) know if this works as expected - and don't forget to close the thread if it does.

Your two separate threads have been merged. Do not post the same question multiple times, it leads to wasting people's time, and I'm sure that's not what you want to happen. Take a moment to read the Forum Rules. Thanks.