Hello All,
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".
Code
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
Display More
Thanks ALL!!!