Don’t have an account yet? Register yourself now and be a part of our community!
I need an VBA help on my existing macro file. Requirement is want to loop the specified files folder from the path and paste in current worksheet. But i want to copy only the first sheet named "Open order report" in the respected folder. With my code i can get all the files in the folder and getting pasted one by one in my sheet but it takes all the sheets in the excel files. I just want only the first sheet "Open order report" of the input need to get copied and to paste in my macro file.
This is the code I have now
Dim InputFileName As String
Dim InputFile As Workbook
Dim ws As Worksheet
Dim SourceRange As Range
Dim TargetRange As Range
Application.ScreenUpdating = False
ChDir ThisWorkbook.Path & "\Desktop/Raw\"
InputFileName = Dir("*.xlsx")
Do Until InputFileName = ""
Set InputFile = Workbooks.Open(InputFileName)
For Each ws In InputFile.Worksheets
Set SourceRange = ws.Range("A1").CurrentRegion
Set SourceRange = SourceRange.Offset(1, 0).Resize(SourceRange.Rows.Count - 1)
Set TargetRange = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize( _
TargetRange.Value = SourceRange.Value
Application.CutCopyMode = False
InputFileName = Dir
Application.ScreenUpdating = True
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False