Copy/Paste Clipboard in VBA

[HangMan]

The attached Workbook which contains a sheet with two pivot tables. I want to convert the pivot tables into normal tables, so the source data is no longer linked. I'm using this technique manually but want to convert it into a macro:

1. Select the pivot table cells and press Ctrl+C to copy the range.
2. Use Paste Special to Paste Values.
3. Display the Office Clipboard.
4. With the unlinked pivot selected, click the item on the Office Clipboard that corresponds to the pivot table copy operation. It will be the last item, unless you copied something else.

I went through the steps and recorded the actions to give me this:

Please Login or Register  to view this content.

However if I then attempt to run the macro on my original pivot tables I get and error (highlighted in red in the code above). Is there anyway to resolve this?

Many thanks

[Leith Ross]

Hello HangMan,

You can copy the values without using Copy/Paste.

Please Login or Register  to view this content.

[HangMan]

Hi Leith,

Okay, though the element missing is the 'Paste Format' from the clipboard, step 4 on the orignal post, this puts the original formatting back onto the table of pasted values. This is what I would like to try and achieve through the macro if it is possible?

Many thanks

[HangMan]

I've constructed this code from various sources which copies specified worksheets from a Workbook to a new Workbook using Paste Values. The only element I can't figure out is how to paste the pivot table formats. This is my code:

Please Login or Register  to view this content.

I've tried adding the code in red but I can't seem to get it to paste the pivot table format, i.e. Paste Clipboard, can anyone please assist?

Please Login or Register  to view this content.

Many thanks

[karedog]

Maybe :

Please Login or Register  to view this content.

[HangMan]

Hi karedog,

How would I amalgamate this with my main piece of code, which is copying other worksheets to the new workbook. Sorry, I'm still very new to VBA?

Many thanks

[HangMan]

I've attempted to integrate your code with my code but I can't get the paste formats for the pivots to work, even though it works with your standalone code, what am I doing wrong?

Please Login or Register  to view this content.

[karedog]

Hi karedog,
.... Sorry, I'm still very new to VBA?
Many thanks

You are welcome HangMan.
Me too

Please Login or Register  to view this content.

[HangMan]

Hi Karedog,

Thanks for this, looking good. There are couple of things, the sheets not containing pivot tables need to be copied using PasteValues and PasteFormats, so there are no formula links to the original sheet. This is what I was struggling with integrating your code with my code.

On the pivots I notice they has dropdown arrows on each column header, is it possoble to remove these completely?

Finally, if I run the code and the file is saved to the specified location and then I run the code again it doesn't warn me that there is already a file with that name and ask if I want to overwrite it, which is baffling me? I think it would actually be far better if it could come up with a dialogue asking the user to enter a filename for the file and select the location if that is possible?

Many thanks

[HangMan]

Okay, I think I've figured out the PasteValue for the other sheets by adding a bit of code back in... but please let me know if this is not a good way to do this? And also figured out how to lose the dropdown arrows and the SaveAs... so beginning to get there...

Just trying to figure out the file saving part... Is this possible?

Please Login or Register  to view this content.

[karedog]

Maybe :

Please Login or Register  to view this content.

[HangMan]

Hi karedog,

So I moved

Please Login or Register  to view this content.

to sit after

Please Login or Register  to view this content.

so it now asks if I want to overwrite the existing file which is great but if I click 'No' then I get an error on

Please Login or Register  to view this content.

how can I prevent the error to allow the user to rename the file?

As an alternative I tried replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

which invokes the Save As dialogue window but if I enter a filename it doesn't save the newly created file using that name, it is still just called Book2 etc., What I'd ideally like is for the Save As dialogue window to appear already populated with the filename thereby allowing the user to append the existing filename, is this possible?

Many thanks once again

[karedog]

From the Excel help file :
Application.GetSaveAsFilename Method
Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

The GetSaveAsFilename only get the string of the filename, it doesn't save, you must call the save method seperately.

Please Login or Register  to view this content.

[HangMan]

Okay, excellent, that is ideal, the only problem now seems to be that if the user hits 'Cancel' instead of 'Save' they can't actually 'Cancel' and make the 'Save As' dialogue window go away? Is there a simple addition to the code to achieve this?

Also, if they do decide they 'do' want to overwrite the existing file by selecting 'Save', they can't because it invokes the 'This File Already Exists' message.

This would need to be a warning that they are about to overwrite the existing file, so they should be able to say 'Yes' and overwrite the file or Rename the file if they don't want to overwrite the existing file or hit 'Cancel' to close the Save As dialogue window altogether without doing either...

Is this possible to achieve?

Many thanks

[karedog]

This is my last post on this thread.

Please Login or Register  to view this content.

Regards

[HangMan]

Hi karedog,

That fixed the cancel problem, there is still a slight problem with the 'Save As' but I appreciate you've spent a long time helping me out with this and this was your last post. Your help is hugely appreciated and there is no way I would have been able to get this far without your help, so thank you...

I'll try and figure out the last little bit on my own.

Many many thanks