Last Used Column in Loop Data Copy

Hello gsurge,

Here is the corrected macro...

Sub datacollect()
  
  Dim C As Long
  Dim DstWks1 As Worksheet
  Dim LastCol As Variant
  Dim LastRow As Long
  Dim R As Long
  Dim SrcWkb As Workbook
  Dim StartRow As Long
  Dim wkbname As Variant
  Dim xlsFiles As Variant
  
   'Starting column and row for the destination workbook
    C = 2
    R = 2
   'Set references to destination workbook worksheet objects
    Set DstWks1 = ThisWorkbook.Worksheets("Data")
    
   'Starting row on source worksheet
    StartRow = 1
    
   'Get the workbooks to open
    xlsFiles = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
    Application.AskToUpdateLinks = False
      If VarType(xlsFiles) = vbBoolean Then Exit Sub
      
     'Loop through each workbook and copy the data to this workbook
      For Each wkbname In xlsFiles
        Set SrcWkb = Workbooks.Open(Filename:=wkbname, ReadOnly:=True)
          Set LastCol = SrcWkb.Worksheets("Equity").Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False)
          If Not LastCol Is Nothing Then LastCol = LastCol.Column Else Continue
          LastRow = SrcWkb.Worksheets("Equity").Cells(Rows.Count, LastCol).End(xlUp).Row
            If LastRow >= StartRow Then
              With SrcWkb.Worksheets("Equity")
                DstWks1.Cells(R, C).Resize(LastRow - StartRow + 1, 1).Value = _
                .Range(.Cells(StartRow, LastCol), .Cells(LastRow, LastCol)).Value
              End With
            End If
        C = C + 1
Continue:
        SrcWkb.Close savechanges:=False
      Next wkbname
      
End Sub

Hello gsurge,

The code used to find the last column searches the worksheet cells column by column starting with the last column and moving left until it finds a cell in a column or it returns back to cell "A1". The "*" is a wildcard meaning any value in the cell.

By checking if the result is "Nothing", the decision is made to either extract the column of the last cell or skip to the next workbook. If the resulting Range object isn't check to be "Nothing", a error will occur.