+ Reply to Thread
Results 1 to 2 of 2

vba code for copy data for all .xls file and copy the data and paste in active work book

  1. #1
    Registered User
    Join Date
    09-17-2019
    Location
    India
    MS-Off Ver
    365
    Posts
    9

    vba code for copy data for all .xls file and copy the data and paste in active work book

    Hi Friends I need help to rectify my code error.
    my query is I have 4(.xls) files in folder and macro file, my code is open xls file one by one and copy the data in my active macro file also rename the tab according the open xls file name, but is not working Please help ..

    Option Explicit

    '-------------------------------------------------------------------------------
    Sub Collect()
    '-------------------------------------------------------------------------------
    'Collect all "Input" sheets from all workbooks in folder.

    Dim DataName As String
    Dim DataWB As Workbook
    Dim File As String
    Dim MasterWB As Workbook
    Dim MScnt As Integer
    Dim Snr As Integer

    'Find out number of sheets in Master
    Set MasterWB = ActiveWorkbook
    MScnt = MasterWB.Sheets.Count

    'Switch to folder containing data workbooks
    'Use path from master for now
    ChDir MasterWB.Path

    'Find al xlsx workbooks in folder
    File = Dir("*.xls")
    While File <> ""
    Debug.Print "Processing file " & File
    'Do not process yourself
    If InStr(File, MasterWB.Name) = 0 Then
    'Open data workbook
    Set DataWB = Workbooks.Open(File, xlUpdateLinksNever, True)
    DataWB.Activate
    'Catch missing input sheet
    On Error Resume Next
    Snr = 1
    'Snr = Sheets("input").Index
    On Error GoTo 0
    If Snr > 0 Then
    Sheets(Snr).Copy After:=MasterWB.Sheets(MScnt)
    MasterWB.Activate
    'Rename added sheet; use data wb name for now
    MScnt = MScnt + 1
    Sheets(MScnt).Name = DataWB.Name
    End If
    MasterWB.Activate
    DataWB.Close False
    End If
    'Next file
    File = Dir()
    Wend
    end sub

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,815

    Re: vba code for copy data for all .xls file and copy the data and paste in active work bo

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA code for copy paste data to active cell
    By keshavtale in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2020, 02:58 AM
  2. Any idea to make this code work faster ( copy and paste data )
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-25-2017, 03:33 AM
  3. [SOLVED] Macro to copy the data from a closed file to another work book using filters
    By Kiran Kurapati in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-26-2014, 12:50 PM
  4. Replies: 0
    Last Post: 01-19-2014, 04:01 PM
  5. [SOLVED] Browse a File, Copy Selected data from it and paste it to other workbook (Active)
    By FCarv in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-24-2012, 09:46 AM
  6. Replies: 3
    Last Post: 03-02-2012, 06:42 AM
  7. Code to Read/Copy Data from Closed Wb and Return/Paste in Active Wb
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2010, 09:26 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1