Adapt Copy/Paste Values Routine

[HangMan]

I originally tried to copy selected worksheets containing a mix of pivot tables and formula driven cells from my main workbook to a new workbook and then copy/paste values so that all the information was 'unlinked' from the orignal workbook and the pivot tables were now just tables and no longer pivot tables.

This all worked well thanks to help from this website, however that approach ran into some problems, so I'm now adopting a different approach. Instead of copying the specified worksheets to a new workbook, I'm simply deleting the unwanted worksheets from the main workbook and resaving the workbook using a different name.

As a result I'm having a few issues with the code that originally copied the worksheets to the new workbook. Instead of copying and pasting values to the new workbook, I need the code to run on the remaining worksheets in the current workbook but I can't get this to work correctly (despite many hours of trying) and I'd be extremely grateful if anyone can help be adjust the code to achieve this.

It is the code in red where I'm having the problems. This is (I think) still trying to copy the specified worksheets in the array to a new workbook, but the code needs to run in the current workbook. I hope this makes sense.

Many thanks

Please Login or Register  to view this content.

[HangMan]

Okay, I'm beginnning to make a little progress and have things kind of working, but I'm getting a 'Run-time Error '1004' Copy Method of Range Failed', affecting both ws.cells elements (shown in red below).

I don't really understand what the error means and why I'm geting it (despite a lot of googling and trying to understand). Can anyone please assist with what I need to do to rectify this problem.

Many

Please Login or Register  to view this content.

[skywriter]

On the cells that you are trying to clear, you should not get a 1004 copy. What is the complete message on that line of code?
Check and make sure that you don't have protected cells on the sheet.
When you say cells you are referring to every single cell on the worksheet, you might want to narrow it down. CurrentRegion, UsedRange something besides just cells.

[AB33]

There are few errors on the code.

' For Each pt In Worksheets(i).PivotTables
should be


For Each pt In Worksheets(arr(i)).PivotTables
Need to use this code to clear data in pivot table.

Please Login or Register  to view this content.

No need to activate a sheet or range.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[skywriter]

You aren't specifying which element in the array, so I would think this line errors also.

Please Login or Register  to view this content.

[HangMan]

Hi AB33,

Okay, I've tried implementing your suggested changes, but they are throwing up other errors (which I'll detail in another post) and I'm still struggling to understand what the problem is. What I've done is to post the original code below, which all worked really well. This was designed to copy selected worksheets (defined in the array) to a new workbook and then copy and paste values and formats, so there were no actual pivot tables, formulas or links back to the original workbook in the new workbook. These worksheets contained a mixture of pivot tables and formula driven tables and charts.

Even though this technique worked well there was a problem where it was impossible to get the charts SERIES source to change to the newly created worksheet. As a result what I'm trying to do now (in the second piece of code) is almost the opposite. Rather than copy the specified worksheets to a new workbook, I'm simply deleting the worksheets I don't want in the original workbook but then attempting to copy/paste values and format for the sheets remaining (which are the ones originally copied to the new workbook above), the ones containing the pivot tables, formula driven tables and charts. This way the original problem with the charts SERIES data not changing to the new workbook should vanish.

I'm sort of assuming part of the problem is that in the original code there were two workbooks, one to copy from the other to copy to, but in my amended version, the code is trying to copy and paste within the same workbook and can't, though I'm not entirely sure. I have tried duplicating the worksheets I want to keep within the same workkbook with a view to trying to copy and paste between the two but again the copy/paste routine still failed.

Original Working Code

Please Login or Register  to view this content.

My attempt at adapting the code above so it deletes the unwanted worksheets from the current workbook and then Copy/Paste Values and Formats for the remaining worksheets and then saves the file with a new name.

Revised Code

Please Login or Register  to view this content.

[HangMan]

Hi skywriter,

That was my confusion, the error I am getting is 'Run-time Error '1004' - Clear Method of Range Class Failed'

On the sheets that remain, the ones that I'm looking to copy/paste values and format, there are no protected cells.

Okay, regarding the range, I've just taken that from the original code which was all working without issue, the range will be different for each individual sheet but I figured as it was pasting values/formats this wouldn't be a problem?

Re

Please Login or Register  to view this content.

The line of code above was all working with no problems in the original code but I've since realised this line is not needed in the updated code, well at least I don't think it is?

I am very new to VBA and am trying to adapt this by trial and error, so I'm not always 100% sure what the code means but tend to test it line by line to try and understand what it is doing, though not always successfully.

[HangMan]

Hi AB33,

Coming back to you re your suggested code changes...

This line doesn't appear to be causing any problems in as much as it isn't tripping the code up and both seem to work, but happy to use the correct code.

Please Login or Register  to view this content.

neither did changing it to this

Please Login or Register  to view this content.

I tried changing the line below in red with the one you suggested (in blue), but this generates a 'Run-time Error '1004' - Unable to get the TableRange2 Property of the PivotTable Class, so I'm unsure if I have not done this correctly?

Please Login or Register  to view this content.

I also tried changing this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

as per your suggestion (although maybe again I'm doing something wrong), but I am getting the same 'Run-time '1004' - Clear Method of Range Class Failed' error.

This is where I am now completly stuck with the code. I'm sure I'm missing something simple and obvious but any help would be gratefully appreciated to get this running correctly.

Many thanks

[AB33]

These 2 are not the same

For Each pt In Worksheets(arr(i)).PivotTables ' looping through the array sheet
and

For Each pt In Worksheets(i).PivotTables ' looping through the sheets using an index no.

Why do you need to complicate by using an array and looping through the array while a simple line could do the job?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[HangMan]

Happy to adopt whatever works for this. My main problem is how to 'fix' the main code to get it to work properly. I have very limited VBA knowledge so am happy to be advised as to the best approach.

[HangMan]

Hi AB33,

I'm still getting nowhere with the code. I take on board what you are saying but my VBA knowledge is still lacking and I don't know which is the best approach to certain tasks and wheter or not an array is better to use or not.

This code creates a new workbook and copies seven worksheets from my original workbook to the new workbook and then copies and pastes the values and formats for the pivot tables on those sheets where it finds them and pastes values for the other sheets and works perfectly.

Please Login or Register  to view this content.

What I want to be able to do is to adapt the code above so that instead of creating a new workbook and copying the seven sheets to the new workbook, it instead deletes all worksheets in my original workbook apart from the seven and then copies and paste values and format for the pivot tables on those sheets within my original workbook.

I can manage to delete the worksheets I need, leaving me with just my seven sheets but the problem I keep coming up against is the copy/paste values and formats. I've tried implementing the code you suggested but I am still getting an error when trying to clear and copy the cells, with both 'ws.Cells.Clear' and 'ws.Cells.Copy'. I also get the same error using your code with 'pt.TableRange2.Clear' so I'm completely stuck and wondered whether you can show me where I am going wrong because I don't understand how to resolve the problem. I tried both an array and your non srray approach but still just get errors. I don't understand why when moving the sheets to a new workbook this all works but when trying to achieve the same thing in the original workbook it errors.

Please Login or Register  to view this content.

Many thanks for your help...

[HangMan]

I think I'm finally making some progress...