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(strFro mRange).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