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

kpev

New Member
Joined
Apr 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,764
Try this macro:
VBA Code:
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

New Member
Joined
Apr 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,764
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

New Member
Joined
Apr 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,764
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

New Member
Joined
Apr 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,764
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

New Member
Joined
Apr 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
It worked!!! Thanks so much!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,142
Messages
5,640,380
Members
417,139
Latest member
bdmprasenjit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top