Consulting

Results 1 to 3 of 3

Thread: VBA Code->copying sheet from mutiple sheets excel into single excel with diff sheets

  1. #1
    VBAX Newbie
    Joined
    May 2018
    Posts
    2
    Location

    VBA Code->copying sheet from mutiple sheets excel into single excel with diff sheets

    Hi,

    i am having 200 excel file in folder ..each file has many sheet...i need 1 sheet (that is one common tab from different excels )from all excels to copied in one excel in different sheets(1 excel sheet data should be copied 1 sheet and second excel data copied in second sheet and so on) not in same sheet. Need it urgent please respond . Thanks

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    
    Sub test()
        Dim ws As Worksheet
        Dim wb As Workbook
        Dim shn As String
        Dim p As String
        Dim f As String
          
        Set ws = Workbooks.Add(xlWBATWorksheet).Sheets(1)
        
        shn = "target"
        p = "C:\*****\*****\"
        
        f = Dir(p & "*.xlsx")
        
        Do While f <> ""
             Set wb = Workbooks.Open(p & f)
            On Error Resume Next
            wb.Worksheets(shn).Copy ws
            ActiveSheet.Name = wb.Name
            On Error GoTo 0
            wb.Close False
            f = Dir()
        Loop
            
        Application.DisplayAlerts = False
        Fws.Delete
        Application.DisplayAlerts = True
          
    End Sub

  3. #3
    VBAX Newbie
    Joined
    May 2018
    Posts
    2
    Location

    Attaching Sample Workbooks

    [QUOTE=mana;380340][CODE]Option Explicit

    Code throwing errors. So I am attaching two workbooks which i am working on..I need country service sheets from both workbooks to copied and pasted in new workbook in different sheet. In New work book sheet1 should have country service details first work book and in sheet 2 Country service details of 2work book..Thanks very much if you can help me in this
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •