VBA indicate in rowTarget the next empty row number.

[davidmg1982]

I have the following code which opens each file in a folder and copy-paste the information to a new file, however is indicated to start pasting the information on row number 2 with rowTarget, how can this be modified to start pasting the information on the next available empty row? Thanks ahead for your advice.

Option Explicit


Const FOLDER_PATH = "C:\Users\David\Documents\BCP DRP\Source Files\"  'REMEMBER END BACKSLASH


Sub ImportWorksheets()
    '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim rowTarget As Long         'output row
   
   rowTarget = 2
   
   'check the folder exists
   If Not FileFolderExists(FOLDER_PATH) Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   
   'set up the target worksheet
   Set wsTarget = Sheets("Project Info and BIA")
   
   'loop through the Excel files in the folder
   sFile = Dir(FOLDER_PATH & "*.xls*")
   Do Until sFile = ""
      
      'open the source file and set the source worksheet - ASSUMED WORKSHEET(1)
      Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
      Set wsSource = wbSource.Worksheets(3) 'EDIT IF NECESSARY
      
      'import the data
      With wsTarget
         .Range("A" & rowTarget).Value = wsSource.Range("B2").Value
         .Range("B" & rowTarget).Value = wsSource.Range("D2").Value
         .Range("C" & rowTarget).Value = wsSource.Range("B3").Value
         .Range("D" & rowTarget).Value = wsSource.Range("E7").Value
         .Range("E" & rowTarget).Value = wsSource.Range("E8").Value
         .Range("F" & rowTarget).Value = wsSource.Range("E9").Value
         .Range("G" & rowTarget).Value = wsSource.Range("E10").Value
         .Range("H" & rowTarget).Value = wsSource.Range("E11").Value
         .Range("I" & rowTarget).Value = wsSource.Range("F7").Value
         .Range("J" & rowTarget).Value = wsSource.Range("F8").Value
         .Range("K" & rowTarget).Value = wsSource.Range("F9").Value
         .Range("L" & rowTarget).Value = wsSource.Range("F10").Value
         .Range("M" & rowTarget).Value = wsSource.Range("F11").Value
         
         'optional source filename in the last column
         .Range("N" & rowTarget).Value = sFile
      End With
      
      'close the source workbook, increment the output row and get the next file
      wbSource.Close SaveChanges:=False
      rowTarget = rowTarget + 1
      sFile = Dir()
   Loop
   
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True
   
   'tidy up
   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
   
   Call DisableWrapText
   
End Sub
Private Function FileFolderExists(strPath As String) As Boolean
    If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

 

[davidmg1982]

Solved:
rowTarget = ActiveWorkbook.Worksheets("Project Info and BIA").Range("A" & Rows.Count).End(xlUp).Row + 1