VBA Excel - Master workbook - drawing data from multiple workbook to make library.

[JAZ91]

I have this code (seen below) that is used to draw data from multiple workbooks into one master workbook.
It clears the content of the master workbook and insert the selected workbook data's-.

The thing I want changed is that I would like to keep adding data from workbooks and not have it clearing all data each time I run the macro.

Therefore I am assuming the code needs to recognize the next free row and then insert the selected data from there on and down.
Lets say that the data in column D or F and G determines whether a row if free, since I'm assuming it would be to much to make the code search each cell in every row to check if the row is free.

My intention is to make a library. The macro should be able to gather data from some item lists that have predetermined column descriptions and the draw the data into a master workbook.
The below code can draw the data but it refreshes the whole sheet every time the macro is used, and therefore cannot be used to make a library.

Sub Træk_data()
  
Worksheets("Item list").Range("A9:BG2000").ClearContents
  
Application.ScreenUpdating = False

    Dim wbIn As Workbook, wbOut As Workbook
    Dim rIn As Range, rOut As Range
    Dim wsIn As Worksheet, wsOut As Worksheet
    Dim diaFolder As FileDialog
    Dim lCount As Long


    Set wbOut = ThisWorkbook
    ' Assuming masterWB has only one sheet

    Set wsOut = wbOut.Sheets(1)
  
        '   get file name for file to process
    MsgBox "Select all the files you want to process by using the Ctrl key and the mouse. "

    ' Open the file dialog to get the  files
    Set diaFolder = Application.FileDialog(msoFileDialogFilePicker)
    With diaFolder
        .AllowMultiSelect = True
        .InitialView = msoFileDialogViewList
        .InitialFileName = sInitialPath
        lCount = .Show
    End With
    If lCount = -1 Then
    ' for each selected file
    For lCount = 1 To diaFolder.SelectedItems.Count
      
        Set wbIn = Workbooks.Open(diaFolder.SelectedItems(lCount))

        'loop through all the sheets in the opened book
      
        For Each wsIn In wbIn.Sheets
      
            'set output range on the Mastersheet to last row
            Set rOut = wsOut.Cells(wsOut.Rows.Count, 1).End(xlUp).Offset(1, 0)
            'now copy the values accross to the Mastersheet
            With wsIn.Range("A9:BG2000")
                rOut.Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With
        Next wsIn
        'close WB
        wbIn.Close savechanges:=False
    Next lCount
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

    'Cleanup
    Set wbIn = Nothing
    Set wbOut = Nothing
    Set rIn = Nothing
    Set rOut = Nothing
    Set wsIn = Nothing
    Set wsOut = Nothing
    Set diaFolder = Nothing
  
End Sub

 

[NdNoviceHlp]

Add the LastRow variable and replace this part of your code. Save a copy of your wb and trial this code. HTH. Dave

Dim LastRow As Integer

For Each wsIn In wbIn.Sheets
Set rOut = wsIn.Range("A9:BG2000")
With wsOut
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A" & LastRow + 1).Resize(rOut.Rows.Count, _
                       rOut.Columns.Count).Cells.Value = rOut.Cells.Value
End With
Next wsin