Copy values when copying a sheet to a new book 2

[Stretchwickster]

Hey people!

I have the following code which successfully copies a worksheet in one workbook to a different workbook. However, the newly copied version displays #NAME? for its values because they are calculated using another sheet from the source workbook. Is there a way to simply copy the values over (and not the formulas) whilst doing a sheet copy?

I know it is possible to do this when copying ranges by using the PasteSpecial command. However, the Worksheet PasteSpecial command doesn't seem to have the Paste parameter that I would normally use when pasting ranges.

Public Sub Save_SheetToNewWB()
  
  Dim SourceWB As Workbook
  Dim NewWB As Workbook
  
  Set SourceWB = ThisWorkbook
  Set NewWB = Workbooks.Add
  SourceWB.Worksheets("CalcSheet").Copy Before:=NewWB.Sheets(1)
  Set NewWB = Nothing
  Set SourceWB = Nothing
  
End Sub

Your help would be much appreciated!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Loomah]

Hi Clive
There may be a nicer way but this would do the trick

Public Sub Save_SheetToNewWB_TWO()
  
  Dim SourceWB As Workbook
  Dim NewWB As Workbook
  
  Set SourceWB = ThisWorkbook
    With SourceWB
      .Worksheets("CalcSheet").Copy Before:=.Sheets(1)
      With .Worksheets(1)
          .UsedRange.Copy
          .Range("A1").PasteSpecial Paste:=xlValues
          Set NewWB = Workbooks.Add
          .Move Before:=NewWB.Sheets(1)
      End With
    End With
  ActiveSheet.Name = "CalcSheet"
  Set NewWB = Nothing
  Set SourceWB = Nothing
  
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Stretchwickster]

Loomah,

I get an error on the Paste line saying:
"This operation requires the merged cells to be identically sized".

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

aaaaaarrrrggghhhhh merged cells - the bane of excel.
IMHO, there is no reason to merge cells. the only benefit is for formatting and that can be achieved by using the "Centre Across Selection" option in the horizontal text section of the alignment tab in the formatting dialog.

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Loomah]

Hi Clive
xlbo has pretty much covered it! Don't understand myself why it isn't possible to copy merged cells and paste them onto themselves but that's the way of it, it seems.

You can get around the problem by unmerging before copying. Doing this won't affect your original sheet but it will affect the copied version. Just use

.UsedRange.MergeCells = False

before the copy line.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

Then you can replace the merge formats by doing a Copy of the original sheet's UsedRange and a PasteSpecial Paste:=xlFormats on the destination's upper-left UsedRange.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stretchwickster]

Thanks for the input guys but I'm still getting an "Unable to set the MergeCells property of the Range class" error.
Here is my current code:

Public Sub Save_SheetToNewWB()

  Dim SourceWB As Workbook
  Dim NewWB As Workbook
  
  Set SourceWB = ThisWorkbook
    With SourceWB
      .Worksheets("CalcSheet").Copy Before:=.Sheets(1)
      With .Worksheets(1)
        .UsedRange.MergeCells = False
        .UsedRange.Copy
        .Range("A1").PasteSpecial Paste:=xlValues
        .UsedRange.Copy
        .Range("A1").PasteSpecial Paste:=xlFormats
        Set NewWB = Workbooks.Add
        .Move Before:=NewWB.Sheets(1)
      End With
    End With
  ActiveSheet.Name = "CalcSheet"
  Set NewWB = Nothing
  Set SourceWB = Nothing
  
End Sub

Anyone got any ideas?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Loomah]

Clive
I'm actually at a loss here. I've made an important amendment to your code (and an unimportant one) but I don't think this will solve your problem I'm afraid.

I have to confess to not being able to recreate your original problem though my test file may have been a little simplified. All that happened from me was that the copied sheet ten created links to the source for the data on a different sheet.

I dread to think that this has anything to do with xl versions - I'm using xl2k on win95. The original code I posted worked ok, the code you have posted worked ok and the code below works! For me!!

I would start by trying elements of the code in a fresh workbook. Try unmerging cells by code, copying & pasting merged ranges (this should still fail!), try copying & pasting formats - merged cells etc. Obviously try stepping through the original code to see that everyting is happening as expected.

Let us know how you get on but I may not be around much today.

Happy Friday
;-)

Public Sub Save_SheetToNewWB()

  Dim SourceWB As Workbook
  Dim NewWB As Workbook
  
  Set SourceWB = ThisWorkbook
    With SourceWB
      .Worksheets("CalcSheet").Copy Before:=.Sheets(1)
      With .Worksheets(1)
        .UsedRange.MergeCells = False
        .UsedRange.Copy
        .Range("A1").PasteSpecial Paste:=xlValues
        'changed here....
        Worksheets("CalcSheet").UsedRange.Copy
        .Range("A1").PasteSpecial Paste:=xlFormats
        Set NewWB = Workbooks.Add
        .Move Before:=NewWB.Sheets(1)
      End With
    End With
  'changed here....
  NewWB.Sheets(1).Name = "CalcSheet"
  Set NewWB = Nothing
  Set SourceWB = Nothing
  
End Sub

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Stretchwickster]

Thanks for your help guys! I solved the problem. I only had a chance to look at it again today - I hadn't touched it for a while.

I'm kicking myself because the unmerge wasn't working due to the sheet being protected! So adding a .Unprotect after the MergeCells=False line solved the problem.

Once again, thanks!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)