New VBA to Import Data

[Jvollmer33]

I am trying to create an Excel Macro that will do the following.
1. Prompt me to choose another excel sheet to to open and allow me to pick the file using MSO FILE DIALOG OPEN
2. CLear the contents of the one of the sheets on my workbook("Data")
3. Take the file that I Choose and copy range A1:1650 and then paste special with formating the cells into the current workbook to sheet("Data")

Right now I have a Macro that does this except that I have to choose the range with a Input box and I dont want to have to do that. Please see below.

Please Login or Register  to view this content.

[JBeaucaire]

Like so:

Please Login or Register  to view this content.

[Jvollmer33]

What if the sourcebook that is opened has a worsheet name that always changes. How can I have the macro just choose that sheet that will be active when the file is opened instead of putting a worksheet name into the macro?

[JBeaucaire]

That's a dangerous approach. But you can replace the workbook.sheet("name") portion of the code with the simple reference to ActiveSheet since it will BE the activesheet once the workbook was just opened.

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Jvollmer33]

I am now getting two errors. Please see code below??

Please Login or Register  to view this content.

[JBeaucaire]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


I corrected your post #1, now you must do the post above.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Jvollmer33]

I am now getting two errors. Please see code below??

Please Login or Register  to view this content.

[JBeaucaire]

I didn't correct any of your RANGE code, A1:1650 is wrong. Perhaps it should be: A1:A1650 or A1:Z1650 ?