VBA - Copying Worksheet from Named workbook and pasting to "Macro-enabled workbook and named worksheet"

jaspalsd

Board Regular
Joined
Feb 3, 2014
Messages
72
Hi,

I have a macro-enabled workbook with a calculation table

These are the steps I need help with.

1. I wish to copy the sheet of a downloaded workbook, however, the workbook is "$XXXX" but when you enable content it changes to "Book1". Ideally I want the original workbook name as it is unique but will the macro work if I ignore the enabled content option? As a side question, why does the filename change when you enable content?

If I enable content then I can't rely on setting the the destination workbook as "Book1" as I imagine if another Excel workbook is open then the filename will change to "Book2"?

2. The sheetname is unique, e.g. "Freexxxx" so I can reference that as well. I wish to copy this to a named worksheet in my macro-enabled. Let's say my workbook is called "FreexxxxCalculator", I presume I'll need the full filename including the file type? e.g. .xlsm. to drill down further I will also like it to be copied over to the worksheet "Calc_Data".

3. I am then able to perform the macro and the calculations which are done on the "Calculator" worksheetname.

4. So once the calculation table is done I then wish to copy this to the original workbook "$Freexxxx" or Book 1 and save as a normal workbook rather than a macro-enabled workbook.
5. ensure the Calculaton workbook is never saved so that everytime the calculation is run it will always calculate new source data.


Putting it simply I wish to copy data from the downloaded workbook (which can have two different filenames depending on enable content) to the calculator workbook - where I run a macro to delete all unnecessary rows (which I've done), apply the data in columns and rows to the Calculator (which I've done) and the end result is to have the original source data and the calculation table in a single workbook on two separate tabs and ensure the calculator workbook closes without saving so the template is untouched.

I hope the above makes sense and I'll appreciate any help!

Thanks,
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,

I've done the macro so this thread can be closed :)

Only issue that remains is the filename changing for enabling the workbook but I'm sure I can find that on another thread!
 
Upvote 0

Forum statistics

Threads
1,218,620
Messages
6,143,523
Members
450,492
Latest member
Rusbus1972

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top