+ Reply to Thread
Results 1 to 2 of 2

Pull content from 2 xlsx files into a master xlsm file under a single MACRO button

  1. #1
    Registered User
    Join Date
    12-15-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    7

    Pull content from 2 xlsx files into a master xlsm file under a single MACRO button

    I have a macro button in ABC.xlsx that would open up another excel file 123.xlsx, copies the content and paste it into a sheet in ABC.xlsx.

    This one works no problem. But I'm having trouble to code under the same button to open up ANOTHER xlsx 456.xlsx to copy its content and paste it in another sheet in ABC.xlsx.

    I get this error when I try to run.

    Compile error: Duplicate declaration in current page. highlighting "wb As Workbook" as bolded below in my code.


    What does this error mean and what should my code be like in order to pull info from 123.xlsx paste in a sheet in ABC.xlsx, then pulls info from 456.xlsx and paste it into another sheet in ABC.xlsx, all under the single button?

    Please advise.

    Here is that part of my code:

    NOTE: Cell G2 contains the path on the harddisk to find 123.xlsx. G1 contains path on hdd to find 456.xlsx.



    '
    ' Copy tooling list (start)
    '

    '

    Application.ScreenUpdating = False
    Sheets("Tools").Select
    Range("A1:Z350").Delete
    Sheets("Core Line").Select
    Workbooks.Open Filename:=Range("G2"), UpdateLinks:=3
    Range("A1:Z350").Select
    Range("A4").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("ABC.xlsm").Activate
    Sheets("Tools").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Core Line").Select
    Range("A1").Select

    Dim wb As Workbook
    For Each wb In Application.Workbooks
    If wb.Name Like "123*" Then wb.Close
    Next wb

    Application.ScreenUpdating = True

    '
    ' Copy tooling list (end)
    '

    '


    OTHER CONTENTS...then.





    '
    ' Copy AFS list (start)
    '

    '

    Application.ScreenUpdating = False
    Sheets("AFS").Select
    Range("A1:Z350").Delete
    Sheets("Core Line").Select
    Workbooks.Open Filename:=Range("G1"), UpdateLinks:=3
    Range("A1:Z350").Select
    Range("A4").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("ABC.xlsm").Activate
    Sheets("AFS").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Core Line").Select
    Range("A1").Select

    Dim wb As Workbook
    For Each wb In Application.Workbooks
    If wb.Name Like "456*" Then wb.Close
    Next wb

    Application.ScreenUpdating = True

    '
    ' Copy AFS list (end)
    '

    '

    OTHER CONTENTS...

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Pull content from 2 xlsx files into a master xlsm file under a single MACRO butto

    First of all, please put code tags around your code.

    If you want another workbook to be opened, declare another variable or use the same variable and allocate the new workbook to this variable.

    You do not need the line dim wb as workbook which you have bolded above.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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