+ Reply to Thread
Results 1 to 9 of 9

Copy & paste source worksheet into exsiting tab in another workbook

  1. #1
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Copy & paste source worksheet into exsiting tab in another workbook

    My Excel VBA knowledge is unfortinately not where I want it to be, however, I am working to resolve this. I have been working a while now (2 days on and off) to create a macro.

    Changing paths for source file & destination - I have integrated a "browse for file" into the macro (which works... wooohooo), as the source data and the "destination" report will both be in different locations every time the macro is used. So as you can guess, the idea of having a path hard coded into the VBA would be useless.

    Need to "copy and paste" the source data (let's call this workbook "Source Mktg", and the worksheet tab "data") which is raw data that begins in cell A1 (headers in row 1), and the number of rows for this source data can vary from month to month, so cannot really use a traditional range. the entire worksheet would be pasted into an existing worksheet (the worksheet tab name will always be called "test", however the name of the workbook will vary, for now let's call the workbook "NY Mktg". However the macro will be run from the "NY Mktg" workbook, so it will always be open when the macro is run. I also want the source workbook to close after the data has been copied to the destination. Oh, and the source data is always a "csv" file.

    Source worksheet name: Source Mktg
    Source tab name: data

    Destination worksheet name: NY Mktg
    Destination tab name: test

    Now I have some VBA written, but I am currently getting an error stating:

    Run-time error '9':
    Subscript out of range

    This is the code I have so far. Please "forgive" the extra "comments" within my code, however this allows me to piece togather other bits of code, so i can determine which section does what (and get excited when one section actually works). It has been very helpful as I learn VBA.

    Please Login or Register  to view this content.
    Thanks in advance for everyone's time and help!
    ~*~ Sherry ~*~
    Poinciana, FL

  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: Copy & paste source worksheet into exsiting tab in another workbook

    When you get the error and you click on Debug, you will see a yellow highlighted line. Which is that line in your code?
    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]

  3. #3
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Copy & paste source worksheet into exsiting tab in another workbook

    When I got that error the following line of code was highlighted yellow:

    Please Login or Register  to view this content.

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

    Re: Copy & paste source worksheet into exsiting tab in another workbook

    Change this part of your code from
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Copy & paste source worksheet into exsiting tab in another workbook

    Okay, I just tried that. I select the file when the "browse dialog" appeared, and the it opened that file in a new workbook (it is named "Book 6" right now), then the "browse dialog" reappeared. Why would this reappear? I already selected my source data from the initial browse dialog, and the destination workbook will always be open when this macro is run. Did I do something wrong?

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

    Re: Copy & paste source worksheet into exsiting tab in another workbook

    Do you have a sample file which you are testing the code on that you can upload ? I am not seeing anything wrong with your code to cause a 2nd browse dialog to appear.

  7. #7
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Copy & paste source worksheet into exsiting tab in another workbook

    Attached is a blank workbook, which is basically what I am working with. I attempted to attach the csv file also, but this upload would not let me, it states that the csv is an invalid file type.

  8. #8
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Copy & paste source worksheet into exsiting tab in another workbook

    I converted my soucre file, which was a "csv" to an "xls" file, so I could provide it. Now the browse for file section at the top would need to be altered since this is an xls file rather than a csv file. But at least there is something to work with.

  9. #9
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Copy & paste source worksheet into exsiting tab in another workbook

    Well, after playing aroudn with the code, and searching online everywhere, I was able to find & adapt some code to meet my needs. This code takes into account that the destination excel file will always be open when the macro is run, and the data is always pasted to the same worksheet (named "test" for this example), and that a path could not be integrated into the code, as the location of the source data will change each time the report is run, so the "browse for file" was a requirement. this code works perfectly for my needs, and I thought I would post it, as someone else could probably use it also.

    Please Login or Register  to view this content.

+ 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