Announcement

Collapse
No announcement yet.

Copy Worksheet As Values Only To New Workbook

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

  • Copy Worksheet As Values Only To New Workbook



    Hello,
    I've managed to figure out part a certain code (or part of it) for copying an existing worksheet to a new workbook but ran into some trouble. Thus far I've adjusted it several ways, and as of now I am trying to have it open into a new workbook, but also copy and paste the data from the original workbook to the new workbook as paste special, values only, as the 1st workbook is linked to bloomberg and therefore contains live data. Below is the code I have so far. Could someone tell me how to modify this so as to achieve what I'm trying to do. I've attached the current code I am using. Thank you.
    fyi :
    'holdings' = original workbook
    'GLV' = original worksheet
    'GLVcurrent'= new workbook
    'GLV' = new worksheet
    Attached Files

  • #2
    Re: Use Macro To Copy And Save Worksheet To New Workbook

    Try this.
    Code:
    Public Sub copy_to_workbook_and_save()
         
        Dim Holdings As Workbook
        Dim NewBook As Workbook
        Dim GLV As Worksheet
        Dim GLVcurrent As Worksheet
         
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
         
        Set Holdings = ThisWorkbook
         
        For Each GLV In Holdings.Worksheets
            GLV.Copy
            
            Set NewBook = ActiveWorkbook
            
            With NewBook
                .SaveAs Filename:="S:\COMPLIANCE\Monitoring\Weekly Monitoring\GLVcurrent.name"
                .Sheets(1).UsedRange.Copy
                .Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
            End With
        Next GLV
        
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
    End Sub
    Boo!

    Comment


    • #3
      Re: Use Macro To Copy And Save Worksheet To New Workbook

      Thank you. But what this is now doing is copying both the 1st and 2nd worksheet in the original workbook, and then giving me an error b/c it cannot save both worksheets under the same name. What I want it to do is copy only the ONE worksheet, which is the 'GLV' worksheet from the original workbook to a new workbook.

      Comment


      • #4
        Re: Use Macro To Copy And Save Worksheet To New Workbook

        The code I posted will only copy one worksheet.
        Boo!

        Comment


        • #5
          Re: Use Macro To Copy And Save Worksheet To New Workbook

          Sorry, but that's not what's happening in my case. It is copying the 1st (GLV) spreadsheet in the original workbook into a new workbook and renaming the workbook to GLV current as I want, but it is also copying the next spreadsheet in that original workbook, and giving me an error message which says:

          Run-time error '1004':
          You cannot save this workbook with the same name as another open workbook or add-in. Choose a different name, or close the other workbook or add-in before saving.

          At this point, if if hit debug then it highlights this line in the code:
          .SaveAs Filename:="S:\COMPLIANCE\Monitoring\Weekly Monitoring\GLVcurrent. name"

          And if I hit End, it closes the error message and I am left with two new workbooks. One named GLVcurrent, and another named Book54 (or whatever #), which contains the 2nd spreadsheet from the original workbook (which again, I am not looking to move). Any advice?

          Comment


          • #6


            Re: Use Macro To Copy And Save Worksheet To New Workbook

            Right I've no idea what you actually have, the previous posts are a little confusing.

            If this GLVcurrent.name here is meant to refer to the the worksheet name you need to alter the code.
            Code:
            .SaveAs Filename:="S:\COMPLIANCE\Monitoring\Weekly Monitoring\GLVcurrent.name"
            To perhaps something like this?
            Code:
            .SaveAs Filename:="S:\COMPLIANCE\Monitoring\Weekly Monitoring\ &" GLV.Name"
            Boo!

            Comment

            Working...
            X