Exporting to new workbook

[sedgely]

i have an excel wb that as lots of formulae, code & external links. i need to create a new wb and copy out a number of sheets to the new wb, BUT i only want the values not any of the formulae, code or links. The sheets that need to be copied vary dependant on the date so i have created a list of sheet names on sheet1 this will be amended to show just the sheets to be exported.
can anyone suggest the best way to go about achieving this?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[PHV]

Have a look at the PasteSpecial method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sedgely]

PH
Looked at pastespecial and have come up with:

Sub CopySheetsToNewWorkbook()
   
   Dim wb As Workbook
   
   Application.DisplayAlerts = False
   
   Set wb = Workbooks.Add(xlWBATWorksheet)
   With wb
      ThisWorkbook.Sheets("mySheet1").Cells.Copy
      .Sheets(1).Cells.PasteSpecial Paste:=xlValues
      .Sheets(1).Cells.PasteSpecial Paste:=xlFormats
      .Sheets(1).Name = "mySheet2"
   End With
   
   Application.CutCopyMode = False
   Application.DisplayAlerts = False
End Sub

what i need to do now is loop thru a range on sheet1("C2:C50") that contains the sheet names i need to copy and then repeat the above code for each of the sheets named. Can you suggest which way to go?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[sedgely]

UPDATE

i have now got as far as:

Sub CopySheetsToNewWorkbook()
   
   Dim wb As Workbook
   Dim sh
   Dim shNo As Integer
   Dim strNewFileName As String
   Dim objSheet As Worksheet
        Set objSheet = Sheets("sHider")
   shNo = 1
   strNewFileName = objSheet.Range("d2").Value _
                    & "SSP_PrePlan_WC_" _
                    & objSheet.Range("e2").Value _
                    & ".xls"
   Application.DisplayAlerts = False
   Application.SheetsInNewWorkbook = objSheet.Range("C65536").End(xlUp).Row - 1
   
   Set wb = Workbooks.Add
   With wb
      For Each sh In Array("mySheet1", "mySheet2", "mySheet3", "mySheet4")
        ThisWorkbook.Sheets(sh).Cells.Copy
        .Sheets(shNo).Cells.PasteSpecial Paste:=xlValues
        .Sheets(shNo).Cells.PasteSpecial Paste:=xlFormats
        .Sheets(shNo).Name = sh
        shNo = shNo + 1
      Next
   End With
   
   Application.CutCopyMode = False
   Application.DisplayAlerts = False
   
   wb.SaveAs strNewFileName
   
   Application.SheetsInNewWorkbook = 3
End Sub

i am struggling now to replace the line

[red]      For Each sh In Array("mySheet1", "mySheet2", "mySheet3", "mySheet4")
[/red]

with something that will look at objSheet.range("c2:c15")and copy the sheets whose names appear in that range.
from earlier posts for a slightly different task i have the following:

Dim arrWs(), i As Long
    With ThisWorkbook
        arrWs = .Sheets("sHider").Range("A2:A50").Value
        On Error Resume Next 'for bad sheet names or missing array items
        For i = LBound(arrWs) To UBound(arrWs)
            If .Sheets.Count > 1 Then
                .Sheets(arrWs(i, 1)).Visible = xlSheetVeryHidden
            End If
        Next i
    End With

but i am not sure how to incorporate this into my new code. Any help would be appreciated

Cheers, Craig
Si fractum non sit, noli id reficere

 

[GlennUK]

i am struggling now to replace the line

      For Each sh In Array("mySheet1", "mySheet2", "mySheet3", "mySheet4")

with something that will look at objSheet.range("c2:c15")and copy the sheets whose names appear in that range.

How about ...

For Each c In objSheet.range("c2:c15")

and using c instead of sh in your loop?


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[firefytr]

If you're going to use an array (hoorah for Array's!) then loop through it with a Long type variable...

For i = Lbound(arr) to Ubound(arr)

-----------
Regards,
Zack Barresse