VBA to copy formula range from a master sheet to all files in a user defined directory

[kpev]

Hi!

I am trying to copy a formula from range A1:H2 in sheet 1 of my master workbook and paste it into the range C1:J2 of the first sheet in all .xls files in a user-defined directory. The .xls files are all uniquely named and have unique sheet names. Thanks in advance for your help!!

This is the code I've cobbled together but it isn't working:

'Declaring variables
Dim FileName, FolderPath, FileArray() As String
Dim Count1, i As Integer
Dim SourceWB, DestWB As Workbook

Set FileDiag = Application.FileDialog(msoFileDialogFilePicker)
With FileDiag
.AllowMultiSelect = True
.Show
End With

If Right(FolderPath, 1) <> "\" Then
FolderPath = FolderPath & "\"
End If

'Getting the file name from the folder
FileName = Dir(FolderPath & "*.xls")

Count1 = 0

'Creating an array which consists of file name of all files in the folder
While FileName <> ""
Count1 = Count1 + 1
ReDim Preserve FileArray(1 To Count1)
FileArray(Count1) = FileName
FileName = Dir()
Wend

Set SourceWB = ThisWorkbook

For i = 1 To UBound(FileArray)

'Opening the workbook
Set DestWB = Workbooks.Open(FolderPath & FileArray(i))

'Pasting the required header
SourceWB.Worksheets(1).Range("A1:H2").Copy DestWB.Worksheets(1).Range("C1:J2")

'Closing the workbook
DestWB.Close True

Next

Set DestWB = Nothing
Set SourceWB = Nothing

End Sub

 

[John_w]

Try this macro:

Option Explicit


Public Sub Copy_Range_To_Workbooks()
    
    Dim selectedFolder As String, fileName As String
    Dim SourceRange As Range
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing destination files"
        .InitialFileName = ThisWorkbook.Path
        If Not .Show Then Exit Sub
        selectedFolder = .SelectedItems(1) & "\"
    End With

    Set SourceRange = ThisWorkbook.Worksheets(1).Range("A1:H2")
    
    fileName = Dir(selectedFolder & "*.xls")
    Do While fileName <> vbNullString
        Workbooks.Open selectedFolder & fileName
        SourceRange.Copy ActiveWorkbook.Sheets(1).Range("C1")
        ActiveWorkbook.Close SaveChanges:=True
        fileName = Dir
    Loop
    
End Sub

 

[kpev]

Thanks for your response. I tried that but instead of copying the formula from range A1: H2 in the source file and pasting it into C1:J2 of the .xls files in the folder, it pasted over C1:J2 of the source file.

 

[John_w]

Is the macro in the source workbook or a separate workbook? Where do you want the macro to be?

Are the destination workbooks in the same folder as the macro workbook and is the macro workbook a .xls file?

 

[kpev]

The macro is in the source workbook where I need to copy the formula range from. I would like it to be in the source workbook. The destination workbooks are in the same folder as the macro workbook and the macro workbook is a .xlsm file.

 

[John_w]

In that case the macro should work as required.

I can't see how it would copy to C1:J2 in the source file.

 

[kpev]

Would you be willing to clarify where in the code it indicates to paste the formula from the source file to the destination file? The rest of the code seems to be working correctly, it just isn't pasting to the new file. Does this command paste the formula range from the source file to the destination file (starting at C1)?

SourceRange.Copy ActiveWorkbook.Sheets(1).Range("C1")

 

[John_w]

Yes, that line copies directly (i.e. not via the Clipboard) from the source file to the destination file.

Try stepping through the code with F8 and check the result of that line.

 

[kpev]

It worked!!! Thanks so much!!!