Paste Macro

[dmac]

How do I write a macro that will paste a range of information (A2:X2) from
various workbooks of the same type and format to the next available line in a
secondary summary workbook? Secondly, if I create this macro in a workbook
and do "save as" will everything translate over correctly when I put in new
information? Basically what I'm doing is using the summary page to link to
Access for cost tracking purposes.

 

[Bernie Deitrick]

dmac,

Copy this sub into an otherwise blank workbook, and then run it, and select the workbooks with the
data when you get the file select dialog.

Sub PullDataFromUserSelectedFiles()
Dim FileArray As Variant
Dim i As Integer
FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
For i = LBound(FileArray) To UBound(FileArray)
Workbooks.Open FileArray(i)
ActiveSheet.Range("A2:X2").Copy
ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
ActiveWorkbook.Close True
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub

HTH,
Bernie
MS Excel MVP