Copy Selected Range From One Sheet To Multiple Workbooks Sheets Using VBA

[olives]

I've been working with a project that I've asked a few questions about here already. This is part of the same project.

I have a master spreadsheeet that has formulas in it to analyze data that is in different workbooks.

The workbooks with the data on them are in the same folder.

The spreadsheets with the data are named Book2 - Book174. xlsx.

I wanted to either select a range, or identify a range, in a code, then have it copied and pasted into the exact same location in all the other spreadsheet files. Is there a code that can do this?

For example, I have a column full of formulas, say in J:J, that I would like to copy to every one of the workbooks, Book2.xslx through Book174.xslx. I would like to paste all the formulas to the same column in every spreadsheet.

Is this difficult? Can the code be made flexible so that if I copy a range then I could paste it a different range I determine, given they are the same size? Overwriting any existent data should not be a problem, because the ranges I would copy to should be blank. Thanks!

[Excel Fox]

Try this

Sub TCall()

    CopyAndPasteToMultipleWorkbooks "SourceSheetName", "J:J", "DestinationSheetName", "DestinationRangeAddress"
    
End Sub


Sub CopyAndPasteToMultipleWorkbooks(strFromSheet As String, strFromRange As String, strToSheet As String, strToRange As String)
     
    Dim strFile As String
    Dim strFileType As String
    Dim strPath As String
    Dim lngLoop As Long
    Dim wbk As Workbook
    
    strPath = "C:\ExcelFox"
    strFileType = "Book*.xlsx" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.doc"
     
    For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
        strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
        Do While strFile <> ""
            If strFile <> ThisWorkbook.Name Then
                Set wbk = Workbooks.Open(strPath & "\" & strFile, False, True)
                With wbk.Sheets(strToSheet)
                    ThisWorkbook.Worksheets(strFromSheet).Range(strFromRange).Copy .Range(strToRange)
                    .Parent.Close 1
                End With
            End If
        Loop
    Next lngLoop
     
    strFile = vbNullString
    strFileType = vbNullString
    strPath = vbNullString
    lngLoop = Empty
     
End Sub

[olives]

Nice! Ok, quick question, with this I can copy range J:J and it will paste straight into Column J of all the spreadsheets, right? Since I only see J:J in the code once, I suppose that as it's written this will copy to the same place where it was copied from - but in the close workbook...

[Excel Fox]

If you wanted to copy a range from Sheet1!J:J of the original workbook, to Sheet2!M:M of all the other closed workbooks, you'd call the function by doing this.

Sub TCall()

    CopyAndPasteToMultipleWorkbooks "Sheet1", "J:J", "Sheet2", "M:M"
    
End Sub