Is it possible to use a VBA module in MSAccess to select certain worksheets in a MSExcel workbook (AssetMaster.xlsb) and copy, combine and save them into a new workbook (AssetMasterAll.xlsb) file as a single worksheet?
Specifically, this is what I would like the module to do...
1. Data to be pasted as values only, stripping any formula or formatting.
2. Tabs to be copied from the AssetMaster.xlsb document include, e.g. Sheet1, Sheet2, Sheet3, Sheet4.
3. Data on each worksheet starts with the same header row (B4:R4). The header row from the first sheet (Sheet1) only needs to be copied to the new document and just the data below this for any of the other worksheets (B5 onwards).
4. Copy data only to the last non-blank row in each sheet (this can vary).
5. The new document, AssetMasterAll.xlsb will have a different file path than the AssetMaster document, e.g. C:\Assets\AssetMaster.xlsb, C:\Reporting\AssetMasterAll.xlsb.
6. Each time the VBA code is run, it will automatically copy and replace the AssetMasterAll.xlsb document (without prompting).
I’ve had no luck in finding code that comes close to what I need and would really appreciate any help with a solution for this.
Thanks in advance.