I am new to the forum and need some assistance with a project I am working on. I get sent 15-20 files a week that I need one column out of based on the value "RjctStatus" in Range A:BA. The issue is that column varies from sheet to sheet.
I need to find that value and copy that entire column to a new workbook to the next available row in column A. I have started the code to loop through all files in the folder. I have tried multiple variations of code with little to no success. I noted inside of the code which code block I need assistance with, with a bold "'This is the code block that I need help with".
Option Explicit Const pFolder = "Removed for security purposes" ' I removed the folder path for security purposes. Sub ImportAllData() 'Get Data from all Excel files in Data Anlysis folder Dim sFile As String 'file to open Dim wsDestination As Worksheet 'file to paste data to. Dim wbSource As Workbook Dim wsSource As Worksheet Dim rowTarget As Long 'output row Dim hyperTarget As Long hyperTarget = 2 Application.ScreenUpdating = False 'confirm the pFolder (path) exists. If Not FileFolderExists(pFolder) Then MsgBox "Specified folder does not exist, Check folder path!" Exit Sub End If 'reset appl settings if error On Error GoTo errHandler Application.ScreenUpdating = False 'set the data destination worksheet Set wsDestination = Sheets(1) 'loop through the Excel files in the folder sFile = Dir(pFolder & "*.xls*") Do Until sFile = "" 'open the source file and set the source worksheet Set wbSource = Workbooks.Open(pFolder & sFile) Set wsSource = wbSource.Worksheets(1) 'import the data from source workbook With wsSource [COLOR=#FF0000][B] 'This is the code block that I need help with[/B][/COLOR] End With 'Hyperlink to the source file With wsDestination .Hyperlinks.Add Anchor:=.Range("L" & hyperTarget), _ Address:=(pFolder & sFile), _ TextToDisplay:="Link to Surce File" End With 'close the source workbook, increment the hyperlink output row and get the next file wbSource.Close SaveChanges:=False hyperTarget = hyperTarget + 1 sFile = Dir() Loop errHandler: On Error Resume Next Application.ScreenUpdating = True 'Clean up Set wsSource = Nothing Set wbSource = Nothing Set wsDestination = Nothing Application.ScreenUpdating = True End Sub