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!

When you get the error and you click on Debug, you will see a yellow highlighted line. Which is that line in your code?

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

Please Login or Register  to view this content.

Change this part of your code from

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

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?

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.

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.

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.

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.