How to set up macro in Excel

[Joe Roberto]

a macro to run:

I have more than 300 excel files in one folder. I like to copy row 75 from
column A to P in each files into one Central file in one worksheet and paste
in one row to the next.

Please help. Thank you,

 

[JP]

Untested but should work. This is air code so test it on a copy of
your workbook. Adjust references as needed.


Const FOLDER As String = "C:\My Files\"

Sub FindBuildingID()
Dim fileName As String

fileName = Dir(FOLDER, vbDirectory)

' loop through folder
Do While Len(fileName) > 0
Call ProcessFile(fileName)
fileName = Dir
Loop

End Sub

Sub ProcessFile(fileName As String)

Dim rng As Excel.Range
Dim cell As Excel.Range
Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim currentWkbk As Excel.Workbook
Dim currentWksht As Excel.Worksheet
Dim currentRange As Excel.Range

Set wkbk = ActiveWorkbook
Set wksht = wkbk.Sheets(1)

' open workbook
Set currentWkbk = Workbooks.Open(FOLDER & fileName)
Set currentWksht = currentWkbk.Sheets(1) ' assume sheet 1, change
as needed
Set currentRange = currentWksht.UsedRange

Dim foundRange As Excel.Range

Set foundRange = currentWksht.Range(Cells(75, 1), Cells(75, 16))

foundRange.Copy wksht.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
currentWkbk.Close False
End Sub


--JP

 

[Don Guillett]

I would use a DIR macro to make a list of the files for col D and then just
copy a formula down

Sub makeformulatogetdatafromclosed()
lr = Cells(Rows.Count, "d").End(xlUp).Row
For Each c In Range("d3:d" & lr)
c.Offset(, 1).Formula = "=[" & c & ".xls]YOURSHEETNAME!$A$75:$P$75"

'c.offset(,1).value=c.offset(,1).value ' change to values if desired
Next c
End Sub--

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)