Taju12 Beginner

  • Member since Jun 9th 2022
  • Last Activity:
Profile Hits
  • Hi all

    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

    Sub GetRaw()

    Dim InputFileName As String

    Dim InputFile As Workbook

    Dim ws As Worksheet

    Dim SourceRange As Range

    Dim TargetRange As Range

    Sheet1.Range("A1").CurrentRegion.Offset(1, 0).Clear

    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( _

    SourceRange.Rows.Count, SourceRange.Columns.Count)

    TargetRange.Value = SourceRange.Value

    Next ws

    Application.CutCopyMode = False


    InputFileName = Dir



    Application.ScreenUpdating = True


    With Selection

    .HorizontalAlignment = xlLeft

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = True

    .ReadingOrder = xlContext

    .MergeCells = False

    End With


    MsgBox "Done"

    End Sub