VBA indicate in rowTarget the next empty row number.

davidmg1982

Board Regular
Joined
Oct 12, 2015
Messages
54
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

davidmg1982

Board Regular
Joined
Oct 12, 2015
Messages
54
Solved:
rowTarget = ActiveWorkbook.Worksheets("Project Info and BIA").Range("A" & Rows.Count).End(xlUp).Row + 1
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,644
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top