Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macro in Excel
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macro in Excel
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 On Sep 23, 3:42*pm, Joe Roberto wrote: 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macro in Excel
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 "Joe Roberto" wrote in message ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoid Outlook macro security for Excel bades macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming |