Results 1 to 19 of 19

Macro to copy two Sheets matching Source to each workbook in matching Destination Folder

Threaded View

prkhan56 Macro to copy two Sheets... 09-30-2017, 04:36 PM
JLGWhiz Re: Macro to copy two Sheets... 10-01-2017, 01:32 PM
prkhan56 Re: Macro to copy two Sheets... 10-01-2017, 07:25 PM
JLGWhiz Re: Macro to copy two Sheets... 10-02-2017, 09:42 AM
prkhan56 Re: Macro to copy two Sheets... 10-02-2017, 07:30 PM
JLGWhiz Re: Macro to copy two Sheets... 10-03-2017, 09:13 AM
prkhan56 Yes you are right. My... 10-03-2017, 10:15 AM
JLGWhiz Re: Macro to copy two Sheets... 10-04-2017, 08:55 PM
prkhan56 Re: Macro to copy two Sheets... 10-05-2017, 05:59 AM
JLGWhiz Re: Macro to copy two Sheets... 10-05-2017, 05:00 PM
prkhan56 Re: Macro to copy two Sheets... 10-05-2017, 07:29 PM
JLGWhiz Re: Macro to copy two Sheets... 10-05-2017, 08:16 PM
prkhan56 Re: Macro to copy two Sheets... 10-06-2017, 01:54 AM
JLGWhiz Re: Macro to copy two Sheets... 10-06-2017, 04:00 PM
prkhan56 Re: Macro to copy two Sheets... 10-06-2017, 05:05 PM
JLGWhiz Re: Macro to copy two Sheets... 10-06-2017, 10:29 PM
prkhan56 Re: Macro to copy two Sheets... 10-07-2017, 06:58 PM
JLGWhiz Re: Macro to copy two Sheets... 10-07-2017, 09:41 PM
prkhan56 Re: Macro to copy two Sheets... 10-08-2017, 04:17 AM
  1. #16
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Macro to copy two Sheets matching Source to each workbook in matching Destination Fold

    This should get the workbook from the second path instead of the first;
    Sub copyStuff4()
    Dim wbsrc As Workbook, wbdst As Workbook, fName As String, sPath As String, dPath As String, pary As Variant
    sPath = "C:\Source\Master\"
    dPath = "C:\Destination\"
    pary = Array("1. Abcd", "2. Efgh", "3. Hij", "4. Klm", "5. Nopq", "6. Rstu", "7. Vx", "8. Wyzab", "9. Cdefghi", "10. Jkl")
    For i = LBound(pary) To UBound(pary)
            Set wbsrc = Workbooks.Open(sPath & pary(i) & "\" & Dir(sPath & pary(i) & "\*.xl*"))
            On Error GoTo Skip:
            fName = Dir(dPath & pary(i) & "\*.xl*")
            Do While fName <> ""
                Set wbdst = Workbooks.Open(dPath & pary(i) & "\" & fName)
                wbsrc.Sheets("Dept").Copy After:=wbdst.Sheets(wbdst.Sheets.Count)
                wbsrc.Sheets("Result").Copy After:=wbdst.Sheets(wbdst.Sheets.Count)
                wbsrc.Close False
                wbdst.Close True
    Skip:
                If Err.Number > 0 Then
                    MsgBox Err.Number & ":  " & Err.Description & vbLf & "Workbook " & fName & " Not processed"
                    wbsrc.Close False
                    Err.Clear
                    On Error GoTo 0
                End If
                fName = Dir    
            Loop
    Next
    MsgBox "All Files Have Been Processed!", vbInformation, "COMPLETE"
    End Sub
    Last edited by JLGWhiz; 10-07-2017 at 12:29 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need a Macro for two sheets, matching and looping, to copy data over.
    By jana_wire in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2017, 03:45 PM
  2. [SOLVED] VBA: Looking for a Macro to Match and Copy from Source to Destination Sheet
    By AliGW in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-01-2016, 03:16 AM
  3. [SOLVED] Copy certain files from Source folder to Destination folder
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2015, 08:56 PM
  4. VBA Copy Paste Cuts From Source, Sends to Ether - Not To Destination Workbook
    By jcaynes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2014, 11:05 AM
  5. Macro to copy entire columns with matching dates from another workbook
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2012, 01:23 PM
  6. [SOLVED] how to copy succeeding rows from source workbook to destination w.
    By chris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2005, 10:06 AM

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