Copy a workbook, but only as value?
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Copy a workbook, but only as value?

  1. #1
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    951
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I know it is possible to copy an entire workbook using worksheets.copy, but is it possible to copy all the sheets without naming them and using the

    .PasteSpecial Paste:=xlValues
    .PasteSpecial Paste:=xlFormats

    This is so that I can copy a workbook without knowing the names of all the sheets (INCLUDING HIDDEN).

    All comments welcome.

    George J

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could say
    for x = 1 to worksheets.count
    sheets(x).range("a1:IV65536").copy

    next x

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i deleted some code let me try again:

    for x = 1 to worksheets.count
    sheets(x).range("a1:IV65536").copy
    (Insert where you want to paste the sheet here)
    next x

  4. #4
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    951
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How could I make this copy the sheets to a new workbook? As I am pretty new to this programming lark I cannot see how to put all the copied sheets into one new workbook.

    Recommendations?
    George J

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you want to copy the entire workbook but name the sheets as Sheet1, Sheet2, etc. instead of the names they already have?

  6. #6
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    951
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I got this from the microsoft news group - I wanted the sheet names copied across, but is there any way to modify this so that the page setup and Headers/footers are also transposed?
    Have been trying
    Worksheets(Tgt).PageSetup.RightHeader = _
    Worksheets(Src).PageSetup.Rightheader
    etc
    but am unable to work it into the code I have. Any pointers appreciated.


    Sub CopyWbkValue()
    '
    ' ValueWbk Macro
    ' Macro recorded 18/03/2002 by gj
    '

    '

    Dim MySheet As Worksheet
    Dim wbNewWb As Workbook
    Dim wbOldWb As Workbook
    Dim shtNewSheet As Worksheet
    Dim TheRange As Range

    Set wbOldWb = ThisWorkbook


    Dim iNumOfNewSheets As Integer
    iNumOfNewSheets = Application.SheetsInNewWorkbook

    With Application
    .StatusBar = "Copying workbook to a new workbook..."
    .ScreenUpdating = False
    .SheetsInNewWorkbook = 1
    Set wbNewWb = Workbooks.Add
    .SheetsInNewWorkbook = iNumOfNewSheets
    End With

    wbNewWb.Sheets(1).Name = "DeleteMeWhenFinished"

    For Each MySheet In wbOldWb.Worksheets
    Set shtNewSheet = wbNewWb.Sheets.Add
    shtNewSheet.Move after:=wbNewWb.Sheets(wbNewWb.Sheets.Count)
    Set TheRange = MySheet.Cells

    shtNewSheet.Name = MySheet.Name
    TheRange.Copy

    With shtNewSheet.Cells
    .PasteSpecial (xlPasteValues)
    .PasteSpecial (xlPasteFormats)
    End With

    shtNewSheet.Range("A1").Select

    If Not MySheet.Visible Then
    shtNewSheet.Visible = xlSheetHidden
    End If

    Next MySheet

    wbNewWb.Sheets(wbOldWb.ActiveSheet.Index).Activate

    With Application
    .CutCopyMode = False
    .DisplayAlerts = False
    wbNewWb.Sheets("DeleteMeWhenFinished").Delete
    .DisplayAlerts = True
    .ScreenUpdating = True
    .StatusBar = False
    End With

    Set MySheet = Nothing
    Set wbNewWb = Nothing
    Set wbOldWb = Nothing
    Set shtNewSheet = Nothing
    Set TheRange = Nothing


    End Sub

    '*****Macro by Mark Driscol

    thanks

    George J


    [ This Message was edited by: George J on 2002-03-18 04:48 ]

  7. #7

    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You have not made it clear as to what you want to do.

  8. #8
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    951
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What I want to do :

    If I have any spreadsheets with formula, I want to be able to create an exact copy of that spreadsheet, but with values instead of formulas (like in paste special - values).

    I would like the page format to be the same as the original, the headers and footers to be pulled from the original and the page layout to be whatever the original was.

    The script above creates the values part ok, but not the header/footer, margins and page layout (Portrait/Landscape).


    George J

  9. #9

    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    All you have to do is use SaveAs to create a copy of your whole workbook, then select all worksheets, select all cells (click on the top left corner of the sheet), and Copy>PasteSpecial/Values.

    You could record a macro or try this :-

    Application.ScreenUpdating = False
    With ActiveWorkbook
    .Save
    .SaveAs Filename:="C:Whatever.xls"
    End With
    Worksheets.Select
    With Cells
    .Copy
    .PasteSpecial Paste:=xlValues
    End With
    Application.CutCopyMode = False
    [A1].Select
    Worksheets(1).Select

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com