Merging Spreadsheets

[JBrucker]

Hello,

I have no prior experience with VBA, but was asked to write a file that will combine data from multiple spreadsheets into one master sheet. Here is what I have so far:

Sub OpenFile()
    Dim sPath As String
    Dim sFile As String
    Dim strName As String
    Dim FinalWB As Workbook
    Dim CopyWB As Workbook
    Dim wsCopy As Worksheet
    Dim wsFinal As Worksheet
   ' Dim iRowFinalWB As Integer
   ' Dim iColFinalWB As Integer
   ' Dim iRowCopyWB As Integer
   ' Dim iColCopyWB As Integer
     
    Set FinalWB = ActiveWorkbook
    Set wsFinal = FinalWB.Sheets(1)
    
    sPath = "C:\joemacro\" 'Change to suit
    sFile = Dir(sPath & "*.xls")
    Do While sFile <> ""
        strName = sPath & sFile
        MsgBox strName 'display file name for debug purposes
        
        Set CopyWB = Workbooks.Open(strName)    ' open the workbook to copy from
        Set wsCopy = CopyWB.Sheets(1)   ' open the sheet to copy from
        
        LastRowCopyWB = wsCopy.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        'set the row where we want to start appending
        'iRowFinalWB = wsFinal.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
         iRowFinalWB = wsFinal.UsedRange.Rows.Count
      
        If iRowFinalWB <> 1 Then
          iRowFinalWB = iRowFinalWB + 1
        End If
        
        'looop through copy data
        For iRowCopyWB = 1 To LastRowCopyWB
        
            'column a
            iColCopyWB = 1  ' set column to copy
            iColFinalWB = 1 ' copy to column B of final work book
            sCellValue = wsCopy.Cells(iRowCopyWB, iColCopyWB).Value
            
            If sCellValue <> "" Then
                FinalWB.ActiveSheet.Cells(iRowFinalWB, iColFinalWB).Value = sCellValue
                bMoveNextRow = True
            End If
        
        
            'column b
            iColCopyWB = 2  ' set column to copy
            iColFinalWB = 2 ' copy to column B of final work book
            sCellValue = wsCopy.Cells(iRowCopyWB, iColCopyWB).Value
            
            If sCellValue <> "" Then
                FinalWB.ActiveSheet.Cells(iRowFinalWB, iColFinalWB).Value = sCellValue
                bMoveNextRow = True
            End If
            
            'column c
            iColCopyWB = 3  ' set column to copy
            iColFinalWB = 3 ' copy to column c of final work book
            sCellValue = wsCopy.Cells(iRowCopyWB, iColCopyWB).Value
            
            If sCellValue <> "" Then
                FinalWB.ActiveSheet.Cells(iRowFinalWB, iColFinalWB).Value = sCellValue
                bMoveNextRow = True
            End If
            
            'column d
            iColCopyWB = 4  ' set column to copy
            iColFinalWB = 4 ' copy to column D of final work book
            sCellValue = wsCopy.Cells(iRowCopyWB, iColCopyWB).Value
            
            If sCellValue <> "" Then
                FinalWB.ActiveSheet.Cells(iRowFinalWB, iColFinalWB).Value = sCellValue
                bMoveNextRow = True
            End If
            
            'column e
            iColCopyWB = 5  ' set column to copy
            iColFinalWB = 5 ' copy to column D of final work book
            sCellValue = wsCopy.Cells(iRowCopyWB, iColCopyWB).Value
            
            If sCellValue <> "" Then
                FinalWB.ActiveSheet.Cells(iRowFinalWB, iColFinalWB).Value = sCellValue
                bMoveNextRow = True
            End If
            
            ' after done copying all of the data for the row, move to the next row in the final wb
            If bMoveNextRow = True Then
                iRowFinalWB = iRowFinalWB + 1
            End If
                        
        Next iRowCopyWB
        
        
        CopyWB.Close False 'Close no save
        sFile = Dir
    Loop
     
    FinalWB.Save
End Sub

What I am having trouble with / don't know how to do is have the macro scan the worksheet for the last filled column, setting that to a variable, and then creating a loop for the columns (as opposed to the individual iterations I have now).

Also, the worksheets are not all in the same format. If there is a way to attempt to standardize these, it would be much appreciated.

Thanks in advance!!!!

 

[oldbrewer]

I would start by getting all worksheets to the same format. Once you have your master sheet I presume you will analyse data?

 

[ChrisM]

Nice job for your first stab at VBA. You just missed one property of the Range object, Range.End. This is equivalent to using CTRL+Arrow keys in the worksheet. It will take you to the last used cell, according to the direction you pick.

So to find the last used column, you could start in column A and work over to the right (as long as you don't have any blank columns in the middle of your data), or you could start waaaay over to the right, maybe column IZ and work to the left.

dim rCell as Range

Set rCell = Range("A1").End(xlToRight)
Set rCell = Range("IZ1").End(xlToLeft)

Now once you have found the last used column, use the same logic to figure out how far down the column goes, using xlDown or xlUp.