Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Files macro
Change
If rwInput.Cells(1, 4) < "" Then to If rwInput.Cells(1,7) < "" Then -- Regards, Tom Ogilvy "Rhonda" wrote in message ... I have a situation where I have created a macro that creates a backup of an excel spreadsheet for each month with a .bak extension. I need to put together all the data in a single table, called Master. The columns a to f and rows 1 to 32 have the exact same info in all sheets. This part is frozen and then there are other columns from g to w which I scroll through which are the same for every sheet. The macro would look in a central location on the server for all the .bak files located here, check in Sheet1, column g, cell by cell, to see if there is any data there, and if so, copy the entire row into the file called Master. The loop continues for all the rows in a spreadsheet and then move on to another file and so on, until done. How can I rework this code to do that? This is what I have: Sub Combine_Files() 'Sub WorksheetCompilation() Dim flName As String, Path As String Dim Master As Range, rgInput As Range, rgOutput As Range, rwInput As Range Dim nRowsOutput As Integer, i As Integer Set Master = ThisWorkbook.Worksheets(1).Range("$a$1") nRowsOutput = 0 Path = InputBox("Enter the path to folder containing workbooks to be compiled") If Right(Path, 1) < "\" Then Path = Path & "\" flName = Dir(Path & "*.bak") If flName = "" Then Exit Sub For i = 1 To 1000 flName = Dir Select Case flName Case "" Exit For Case "Master.bak" Case Else Workbooks.Open Filename:=Path & flName With ActiveWorkbook.Worksheets("Sheet1").UsedRange For Each rwInput In .Rows If rwInput.Row = 1 Then Else If rwInput.Cells(1, 4) < "" Then rwInput.Cells.Copy Destination:=Master.Offset(nRowsOutput, 0) nRowsOutput = nRowsOutput + 1 Else End If End If Next rwInput ActiveWorkbook.Close savechanges:=False End With End Select Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I combine several excel files into one? | Excel Worksheet Functions | |||
combine 2 files into one | Excel Worksheet Functions | |||
How to combine Word97 files? | Excel Discussion (Misc queries) | |||
Another combine two files question.... | Excel Discussion (Misc queries) | |||
combine two files | Excel Discussion (Misc queries) |