Paste one of sheet from multiple files on one new file

[PRADEEPB270]

Hi,

I have 5 micro enabled files .

But here I am attaching 2 files,My query is how to paste one of sheet of one file on an new file.

For an example,my file name are:

1-AYU
@-ARU

and try to paste one of the sheet name "others-cash "from row no.4 to till end and same will be others files.on a new file i.e.file name PG.

Can we paste one of sheet from multiple files on a new file.

Here want to paste one of the sheet name-others-Cash from AYU and ARU files on File name-PG with file name also.


r file

[HansV]

Here is a macro:

Code: Select all

Sub Combine()
    Dim strPath As String
    Dim varFile
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim fr As Long
    Dim lr As Long
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Dim t As Long
    Dim blnFirst As Boolean
    Application.ScreenUpdating = False
    ' Specify another path if needed
    strPath = ThisWorkbook.Path & "\"
    Set wbkT = Workbooks.Add(Template:=xlWBATWorksheet)
    Set wshT = wbkT.Worksheets(1)
    wshT.Name = "Combined"
    t = 4
    blnFirst = True
    For Each varFile In Array("AYU", "ARU")
        Set wbkS = Workbooks.Open(Filename:=strPath & varFile & ".xlsm")
        Set wshS = wbkS.Worksheets(1)
        If blnFirst Then
            fr = 4
            blnFirst = False
        Else
            fr = 5
        End If
        lr = wshS.Range("B" & wshS.Rows.Count).End(xlUp).Row
        wshS.Range("B" & fr & ":L" & lr).Copy Destination:=wshT.Range("C" & t)
        wshT.Range("B" & t).Resize(lr - fr + 1).Value = varFile
        t = t + lr - fr + 1
        wbkS.Close SaveChanges:=False
    Next varFile
    wshT.Range("B1:M1").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

[PRADEEPB270]

Working good but all the sheets have been pasted.

Only one sheet i.e.Others-Cash should be pasted from ARU and AYU files and not other sheets.

Please refer my attachment file namely-PG

[HansV]

Change

Code: Select all

        Set wshS = wbkS.Worksheets(1)

to

Code: Select all

        Set wshS = wbkS.Worksheets("Others-Cash ")

[PRADEEPB270]

Now it is working perfectly.

Thanks for the help.

Regards