Hello
I apologise in advance if this has been covered on this forum before but I have looked and not been able to find what it is I would like to do.
I have a workbook with 4 worksheets 'Info', 'Copy', 'LookUp' & 'Main'. 'Info' contains an array formula which brings in a large amount of data from another workbook. 'Copy' is the pasted values of 'info'. 'LookUp' contains lookup data for the array in 'info' and 'Main' contains a Pivot based on the data in 'copy' and also a button that runs a macro to copy and paste values from the 'info' sheet to the 'copy' sheet.
I would like another button on the 'main' sheet to save a copy to a destination from a cell value say D1 in 'main' sheet, with a file name from another cell in 'main' say D2 and I would like this new copy to omit the 'info' worksheet and only save the 'Copy', 'LookUp' & 'Main' sheets (I want it to omit the 'info' sheet so it doesn't bring in the array formulas that might slow down the new copy and also the pop up box asking to update links etc).
If its easier, then simply copying and pasting values for the 'info' sheet into the new workbook would also work (which ever is faster)
I would also like it to keep the formatting of these three sheets so that the pivot table remains on the 'main' sheet. It would also be ideal to remove all the macros form the saved file and for this new copy to not open up at all.
So.
1. Make a copy of a worksheet using cell references for the destination and file name
2. Omit a specific worksheet from this new copy
3. Keep formatting of remaining worksheets (one of which contains a pivot)
4. Remove macros from new copy and make sure the copy doesn't open (ie all this happens in the background)
I have tried my best to explain this as fully as I cant so Im sorry if its long winded and if this has been asked before.
Any help will be greatly appreciated.
Thanks in advance