Announcement

Collapse
No announcement yet.

copy sheet values not formulas

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • copy sheet values not formulas



    Hi

    I'm using the code below to copy 2 worksheets into a new workbook so it can be emailed by the user to me.

    One of the sheets (Statistics), when its sent to me, most of the cells has #VALUE! instead of a figure because its trying to link to the sheet the user still has, if you get my drift.

    Could someone make a suggestion on what changes I need to make to my code so the values, rather than the formulas are copied into the new book.

    I have used the .PasteSpecial xlPaste...... code before but unsure how to make it work on a sheet and not just a range of cells.

    Any help you folks can give will be much appreciated.

    Thanks again

    Philip

    Code:
    Sub eMailActiveWorksheet()
         
        Dim OL As Object
        Dim EmailItem As Object
        Dim Wb As Workbook
        Dim FileName As String
        Dim y As Long
        Dim TempChar As String
        Dim SaveName As String
         
        Application.ScreenUpdating = False
        Set OL = CreateObject("Outlook.Application")
        Set EmailItem = OL.CreateItem(olMailItem)
        FileName = ActiveSheet.Name & ".xls"
        For y = 1 To Len(FileName)
            TempChar = Mid(FileName, y, 1)
            Select Case TempChar
            Case Is = "/", "\", "*", "?", """", "<", ">", "|"
            Case Else
                SaveName = SaveName & TempChar
            End Select
        Next y
        currentbook = ActiveWorkbook.Name
        ActiveSheet.Copy
        newbook = ActiveWorkbook.Name
        Windows(currentbook).Activate
        Sheets("STATISTICS").Copy Before:=Workbooks(newbook).Sheets("Planning")
        Sheets("Planning").Activate
        Set Wb = ActiveWorkbook
        Wb.SaveAs "Email " & ActiveSheet.Name & "_" & Wb.Name & ".xls"
        Wb.ChangeFileAccess xlReadOnly 'Leave this line out to allow user to write and save
        With EmailItem
            .Subject = "Updated Tracker for " & ActiveSheet.Name
            .To = ""
            .CC = ""
            .Attachments.Add Wb.FullName
            EmailItem.Display
        End With
        Kill Wb.FullName
        Wb.Close False
         
        Application.ScreenUpdating = True
         
        Set Wb = Nothing
        Set OL = Nothing
        Set EmailItem = Nothing
         
    End Sub

  • #2
    Re: copy sheet values not formulas

    How about this?

    Code:
    Dim CopiedSheet As Worksheet
    Set CopiedSheet = Sheets("STATISTICS").Copy Before:=Workbooks(newbook).Sheets("Planning")
    
    CopiedSheet.UsedRange.Copy
    CopiedSheet.Range("A1").PasteSpecial Paste:=xlValues
    Maybe a bit of a cheat, but it basically copies the sheet, (as you were doing before) and then copies it again and pastes values.

    Hope this helps

    Alastair
    Einstein:
    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

    Comment


    • #3
      Re: copy sheet values not formulas

      Alastair

      Thanks for getting back to me so quickly.

      I inserted your code but the line
      Code:
      Set CopiedSheet = Sheets("STATISTICS").Copy Before:=Workbooks(newbook).Sheets("Planning")
      turns red and when I run the code I get a syntax error message.

      Philip

      Comment


      • #4
        Re: copy sheet values not formulas

        Alternatively:

        Code:
        Sheets("STATISTICS").UsedRange.Copy
        Set NewSheet = Sheets.Add (Sheets("Planning"))
        NewSheet.Range("A1").PasteSpecial Paste:= xlPasteValues
        NewSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        This method will stop the VALUE errors being copied, as it breaks the links first.

        Hope this helps.

        Alastair
        Einstein:
        Things should be made as simple as possible, but not any simpler

        Be sure to check out TemplateZone for all your Microsoft Office Needs.
        Get OfficeReady Professional 3.0 here!

        Comment


        • #5
          Re: copy sheet values not formulas

          I inserted your code but the line
          Code:
          Code:
          Set CopiedSheet = Sheets("STATISTICS").Copy Before:=Workbooks(newbook).Sheets("Planning")
          turns red and when I run the code I get a syntax error message.
          Sorry, try this:
          Code:
          Set CopiedSheet = Sheets("STATISTICS").Copy (Before:=Workbooks(newbook).Sheets("Planning"))
          Forgot the brackets around the Before part!

          Alastair
          Einstein:
          Things should be made as simple as possible, but not any simpler

          Be sure to check out TemplateZone for all your Microsoft Office Needs.
          Get OfficeReady Professional 3.0 here!

          Comment


          • #6
            Re: copy sheet values not formulas

            Alastair

            Sorry, I tried all your suggestions and couldnt get any of them to work.

            Should I be deleting any of my original code before inserting yours or is yours in addition to mine?

            Apologies for asking what may seem to be a basic question

            Philip

            Comment


            • #7
              Re: copy sheet values not formulas

              Hi Philip, use this code instead of the code that you posted originally.

              Code:
              Sub eMailActiveWorksheet()
                   
                  Dim OL As Object
                  Dim EmailItem As Object
                  Dim Wb As Workbook
                  Dim FileName As String
                  Dim y As Long
                  Dim TempChar As String
                  Dim SaveName As String
                   
                  Application.ScreenUpdating = False
                  Set OL = CreateObject("Outlook.Application")
                  Set EmailItem = OL.CreateItem(olMailItem)
                  FileName = ActiveSheet.Name & ".xls"
                  For y = 1 To Len(FileName)
                      TempChar = Mid(FileName, y, 1)
                      Select Case TempChar
                      Case Is = "/", "\", "*", "?", """", "<", ">", "|"
                      Case Else
                          SaveName = SaveName & TempChar
                      End Select
                  Next y
                  currentbook = ActiveWorkbook.Name
                  ActiveSheet.Copy
                  newbook = ActiveWorkbook.Name
                  Windows(currentbook).Activate
                  
                  'Sheets("STATISTICS").Copy Before:=Workbooks(newbook).Sheets("Planning")
                  'Sheets("Planning").Activate
                  
                  '-----------------------------------------------------------------------------------------------------------
                  'New Code
                  Sheets("STATISTICS").Range("a1:iv65536").Copy
                  Set NewSheet = Sheets.Add(Workbooks(newbook).Sheets("Planning"))
                  NewSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
                  NewSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
                  Application.CutCopyMode = False
                  '-----------------------------------------------------------------------------------------------------------
                  
                  Set Wb = ActiveWorkbook
                  Wb.SaveAs "Email " & ActiveSheet.Name & "_" & Wb.Name & ".xls"
                  Wb.ChangeFileAccess xlReadOnly 'Leave this line out to allow user to write and save
                  With EmailItem
                      .Subject = "Updated Tracker for " & ActiveSheet.Name
                      .To = ""
                      .CC = ""
                      .Attachments.Add Wb.FullName
                      EmailItem.Display
                  End With
                  Kill Wb.FullName
                  Wb.Close False
                   
                  Application.ScreenUpdating = True
                   
                  Set Wb = Nothing
                  Set OL = Nothing
                  Set EmailItem = Nothing
                   
              End Sub
              Apologies for not being clearer originally, I assumed from the level of coding you posted you would know what to do

              Hope this helps

              Alastair
              Einstein:
              Things should be made as simple as possible, but not any simpler

              Be sure to check out TemplateZone for all your Microsoft Office Needs.
              Get OfficeReady Professional 3.0 here!

              Comment


              • #8
                Re: copy sheet values not formulas

                Sorry Alastair - I have received a lot of help since January from this site and some bits of code i can understand (up to a point) and amend, but sometimes what to most people is very basic to me is difficult for me.

                Tried the code you so helpfully amended for me and I get this error "runtime error 1004 method "Add" of object "Sheets" failed on this line of code:
                Code:
                Set NewSheet = Sheets.Add(Workbooks(newbook).Sheets("Planning"))
                I bet you are starting to wish you hadnt replied to me now but for all your help so far

                Philip

                Comment


                • #9
                  Re: copy sheet values not formulas

                  Try replacing this:
                  Code:
                  newbook = ActiveWorkbook.Name
                  with this:
                  Code:
                  newbook = ActiveWorkbook.Name & ".xls"
                  Hope that helps,

                  Alastair

                  PS:
                  sometimes what to most people is very basic to me is difficult for me
                  If you can understand any of it, and are prepared to try to understand you are better than 'most people'! A lot of people seem to get scared off at any sight of VBA. Therefore well done on gettin this far.
                  Einstein:
                  Things should be made as simple as possible, but not any simpler

                  Be sure to check out TemplateZone for all your Microsoft Office Needs.
                  Get OfficeReady Professional 3.0 here!

                  Comment


                  • #10
                    Re: copy sheet values not formulas

                    Scratch that last post. I have just run a few test codes, and it was wrong... It's strange, on my machine my code is working perfectly. Though it is harder to check that I haven't missed something elsewhere... Will keep looking.

                    Alastair
                    Einstein:
                    Things should be made as simple as possible, but not any simpler

                    Be sure to check out TemplateZone for all your Microsoft Office Needs.
                    Get OfficeReady Professional 3.0 here!

                    Comment


                    • #11
                      Re: copy sheet values not formulas

                      Apologies for all these posts...

                      Just out of interest what is this for?

                      Code:
                          currentbook = ActiveWorkbook.Name 
                          ActiveSheet.Copy 
                          newbook = ActiveWorkbook.Name 
                          Windows(currentbook).Activate
                      'currentbook' is exactly the same as 'newbook', and 'currentbook' is already activated. You may need to make sure that the sheets in this
                      Code:
                      Set NewSheet = Sheets.Add(Workbooks(newbook).Sheets("Planning"))
                      are in the correct workbooks.

                      Alastair
                      Einstein:
                      Things should be made as simple as possible, but not any simpler

                      Be sure to check out TemplateZone for all your Microsoft Office Needs.
                      Get OfficeReady Professional 3.0 here!

                      Comment


                      • #12
                        Re: copy sheet values not formulas

                        Alastair

                        'currentbook' is the spreadsheet (called "Planning Tracker.xls") which activates the eMailActiveWorksheet code. The "Planning" sheet is copied and pasted into a newly created workbook ('newbook') and becomes the active workbook. The code then activates "currentbook" ("Planning Tracker.xls"), copies the worksheet "STATISTICS" and pastes it into 'newbook'. Eventually 'newbook' gets inserted in an email for sending.

                        Maybe this is why I get the errors with your code - maybe its not picking up the correct workbook to copy or paste the 'STATISTICS' sheet as values.

                        And thanks for your comment earlier - I never even knew VBA existed until January this year and I have used a lot of it - granted most from here - but I find it very interesting and seem to be learning something knew every week.

                        Philip

                        Comment


                        • #13
                          Re: copy sheet values not formulas

                          This is fairly defintely where your problem is then.

                          Currently newbook and currentbook are the same thing. you need to add a line in the section I pointed out that activates the new book. Something like:

                          Code:
                          currentbook = ActiveWorkbook.Name 
                          'This line is redundant
                          'ActiveSheet.Copy 
                          Workbook("Book1.xls").Activate
                          newbook = ActiveWorkbook.Name 
                          Windows(currentbook).Activate
                          This can be shortened to:
                          Code:
                          Dim currentbook, newbook As Workbook
                          
                          Set currentbook = Workbook("Planning Tracker.xls")
                          Set newbook = Workbook("Book1.xls")
                          This means your code should look like this:

                          Code:
                          Sub eMailActiveWorksheet()
                               
                              Dim OL As Object
                              Dim EmailItem As Object
                              Dim FileName As String
                              Dim y As Long
                              Dim TempChar As String
                              Dim SaveName As String
                              Dim currentbook, newbook As Workbook
                              Dim MyNewSheet As Worksheet
                               
                              Application.ScreenUpdating = False
                              Set OL = CreateObject("Outlook.Application")
                              Set EmailItem = OL.CreateItem(olMailItem)
                              FileName = ActiveSheet.Name & ".xls"
                              For y = 1 To Len(FileName)
                                  TempChar = Mid(FileName, y, 1)
                                  Select Case TempChar
                                  Case Is = "/", "\", "*", "?", """", "<", ">", "|"
                                  Case Else
                                      SaveName = SaveName & TempChar
                                  End Select
                              Next y
                              
                              Set currentbook = Workbooks("Planning Tracker.xls")
                              Set newbook = Workbooks("Book1.xls")
                              
                              currentbook.Sheets("STATISTICS").Range("a1:iv65536").Copy
                              Set MyNewSheet = Sheets.Add(newbook.Sheets("Planning"))
                              newbook.MyNewSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
                              newbook.MyNewSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
                              Application.CutCopyMode = False
                               
                              newbook.SaveAs "Email " & newbook.Name & "_" & currentbook.Name & ".xls"
                              newbook.ChangeFileAccess xlReadOnly 'Leave this line out to allow user to write and save
                              With EmailItem
                                  .Subject = "Updated Tracker for " & newbook.Name
                                  .To = ""
                                  .CC = ""
                                  .Attachments.Add currentbook.FullName
                                  EmailItem.Display
                              End With
                              Kill currentbook.FullName
                              currentbook.Close False
                               
                              Application.ScreenUpdating = True
                               
                              Set currentbook = Nothing
                              Set newbook = Nothing
                              Set OL = Nothing
                              Set EmailItem = Nothing
                               
                          End Sub
                          This is untested, and you will need to check which workbooks are being referenced in each case. I have deleted the WB variable since it was already covred by the newbook and currentbook.

                          Hope this helps

                          Alastair
                          Einstein:
                          Things should be made as simple as possible, but not any simpler

                          Be sure to check out TemplateZone for all your Microsoft Office Needs.
                          Get OfficeReady Professional 3.0 here!

                          Comment


                          • #14
                            Re: copy sheet values not formulas

                            Alastair

                            Thanks for helping me out with this one - I really appreciate it.

                            I tried your code this morning and it was getting stuck at this line:
                            Code:
                            Set newbook = Workbook("Book1.xls")
                            So I used F8 and went thu each line of code - I hope you dont mind but I changed your code slightly and did put in the line
                            Code:
                            ActiveSheet.Copy
                            because it seemed to activate the creation of a new workbook with that sheet copied into it.

                            The last part of your code killed the wrong workbook and of course i hadnt saved my changes and ended up having to start again - oops I should have checked first.

                            Anyway - here is the code I ended up with and it seems to work ok now
                            Code:
                            Sub eMailActiveWorksheet()
                                 
                                Dim OL As Object
                                Dim EmailItem As Object
                                Dim FileName As String
                                Dim y As Long
                                Dim TempChar As String
                                Dim SaveName As String
                                Dim currentbook, newbook As Workbook
                                Dim MyNewSheet As Worksheet
                                 
                                Application.ScreenUpdating = False
                                Set OL = CreateObject("Outlook.Application")
                                Set EmailItem = OL.CreateItem(olMailItem)
                                FileName = ActiveSheet.Name & ".xls"
                                For y = 1 To Len(FileName)
                                    TempChar = Mid(FileName, y, 1)
                                    Select Case TempChar
                                    Case Is = "/", "\", "*", "?", """", "<", ">", "|"
                                    Case Else
                                        SaveName = SaveName & TempChar
                                    End Select
                                Next y
                                
                                Set currentbook = Workbooks("Planning Tracker.xls")
                                ActiveSheet.Copy
                                Set newbook = ActiveWorkbook
                                
                                currentbook.Sheets("STATISTICS").Range("a1:b76").Copy
                                Set MyNewSheet = Sheets.Add(newbook.Sheets("Planning"))
                                MyNewSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
                                MyNewSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
                                MyNewSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                                Application.CutCopyMode = False
                                Sheets("Sheet1").Name = "STATISTICS"
                                Sheets("Planning").Activate
                                 
                                newbook.SaveAs "Email " & currentbook.Name
                                newbook.ChangeFileAccess xlReadOnly 'Leave this line out to allow user to write and save
                                With EmailItem
                                    .Subject = "Updated Tracker for " & ActiveSheet.Name & " " & Int(Now)
                                    .To = ""
                                    .CC = ""
                                    .Attachments.Add newbook.FullName
                                    EmailItem.Display
                                End With
                                Kill newbook.FullName
                                newbook.Close False
                                 
                                Application.ScreenUpdating = True
                                 
                                Set currentbook = Nothing
                                Set newbook = Nothing
                                Set OL = Nothing
                                Set EmailItem = Nothing
                                 
                            End Sub
                            Thanks again Alastair for all your help and pointing me in the right direction.

                            Philip

                            PS just about to try it on a Windows 2000 pc to make sure it works ok - u never know i may be back!

                            Comment


                            • #15


                              Re: copy sheet values not formulas

                              Glad it works, and thanks for the feedback!

                              Alastair
                              Einstein:
                              Things should be made as simple as possible, but not any simpler

                              Be sure to check out TemplateZone for all your Microsoft Office Needs.
                              Get OfficeReady Professional 3.0 here!

                              Comment

                              Working...
                              X