Results 1 to 6 of 6

How to use Getopenfilename to switch between workbooks

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    How to use Getopenfilename to switch between workbooks

    Hi guys.

    Not sure if this is a good thing.. I frequent the forum a lot but dont' really post too much as I usually tend to find my solutions on here.
    There are quite a few threads on copy-pasting data between 2 workbooks but I unfortunately can't seem to extend that code to suit my needs.

    I am trying to write a macro to do the following:
    open 2 workbooks (path and filename changes each month). The user will select the files using getopenfilename.
    Then I want the macro to copy specific cells from each of these workbooks (am ok to write my own code for this), and then paste it back in the third workbook, which is the active workbook where I'll be keeping the macro button to execute the code.

    I'm not sure how to refer to the workbooks in the code.
    I've tried something along these lines but can't seem to get it to work

    I've tried using the Windows.Activate code also but I suspect my syntax is wrong.
    (I'm an accountant learning VBA from scratch, so please consider my skill level as beginner to VBA. Am very much above average user at non-VBA stuff though able to write complex SUMPRODUCTS etc.)

    Here is the code I compiled up to now.
    This should be enough to give an idea of what I'm trying to achieve.

    Sub codetest()
        
        
        Dim BillCalcs As String 'this is the activeworkbook where info will be pasted
        Dim MEWBC As String 'this is workbook 1 to copy from
        Dim MEWBP As String 'this is workbook 2 to copy from
        
        'specify which files to use as a source
        MEWBC = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is CURRENT Mth Wkbk")
        MEWBP = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is PRIOR Mth Wkbk")
        
        Workbooks.Open (MEWBC), UpdateLinks:=0
        Workbooks.Open (MEWBP), UpdateLinks:=0
        
        'this is where it fails :(
        Workbooks(MEWBC).Activate
        
        Sheets("CUSTOMSHEETNAME").Select
        range("B13:B100").Select
        Selection.Copy
        
        Workbooks(BillCalcs).Activate
        ActiveSheet.Select
        Selection.Paste
    
    End Sub
    Last edited by rasonline; 08-27-2013 at 04:12 AM. Reason: Grammar, change heading, SOLVED

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 01-29-2013, 07:01 AM
  2. Copy/Paste range of data excluding certain rows and copy to another workbook
    By HoerbigAdm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2012, 02:51 PM
  3. [SOLVED] Match Copy & Paste Between 2 Wkbks Revised
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2011, 06:12 AM
  4. Copy data from workbook, create new workbook, paste data to new worbook?
    By mpkavanagh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-16-2011, 06:39 PM
  5. Copy paste WkBk/sheet 1 to multiple wkbks/sheets
    By wrpalmer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2005, 11:05 AM

Tags for this Thread

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