T
tajullrich
Good day! I would first like to thank everyone that has posted to this
group as I have learned much by using it.
I would like to create a process that allows the user to select from a
group of cells which contains a list of the worksheets and saves those
worksheets selected (or deletes those not selected). I created the
macro below that works from a command button. Right now I can save all
the worksheets to a new book and name the workbook based on information
provided by the user. However, I can't work out how to remove all the
worksheets that are NOT selected in the list.
For example, say we have on our Cover sheet in cells A20, A21, A22, A23
values 'SheetA', 'DataB', 'MyC',' and 'AnotherD' which correspond to
the names of each sheet in the workbook. The user wants to save 'DataB'
and 'AnotherD' to another workbook, so they select those and click the
macro. I want the cover, 'DataB' and 'AnotherD' saved (or remaining) in
the new workbook.
Below is code I have so far. I haven't been able to come up with how to
procede and code.
Thank you in advance for all you help.
Sub ExporttoNewBook()
On Error GoTo cmdPublish_Click_ERR
Selection.Select
Dim wksht As Worksheet
'SAVE WORKBOOK TO STRING PROVIDED IN COVER SHEET NAMED RANGE
'PublishFile'
Dim PublishFile As String
PublishFile = Range("PublishFile").Value ' PublishFile is obviously a
named range
If Len(PublishFile) <> 0 Then
ThisWorkbook.Save
ThisWorkbook.SaveAs PublishFile
'PASTE SPECIAL TO REMOVE FORMULAS
For Each wksht In ThisWorkbook.Worksheets
wksht.Cells.Copy
wksht.Range("A1").PasteSpecial xlPasteValues
Next
'THIS IS WHERE I'M STUCK!!!!!! If there is another way, please adivse
' For Each wksht In ThisWorkbook.Worksheets
' With Selection
' If (wksht.Name = Selection) Then
' wksht.Cells.Copy
' wksht.Range("A1").PasteSpecial xlPasteValues
' End If
' End With
' Next
ThisWorkbook.Save
Else
MsgBox "Please supply a file name to publish as.", vbOKOnly +
vbCritical, "All Stop"
End If
Exit Sub
cmdPublish_Click_ERR:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical,
"All Stop"
End Sub
group as I have learned much by using it.
I would like to create a process that allows the user to select from a
group of cells which contains a list of the worksheets and saves those
worksheets selected (or deletes those not selected). I created the
macro below that works from a command button. Right now I can save all
the worksheets to a new book and name the workbook based on information
provided by the user. However, I can't work out how to remove all the
worksheets that are NOT selected in the list.
For example, say we have on our Cover sheet in cells A20, A21, A22, A23
values 'SheetA', 'DataB', 'MyC',' and 'AnotherD' which correspond to
the names of each sheet in the workbook. The user wants to save 'DataB'
and 'AnotherD' to another workbook, so they select those and click the
macro. I want the cover, 'DataB' and 'AnotherD' saved (or remaining) in
the new workbook.
Below is code I have so far. I haven't been able to come up with how to
procede and code.
Thank you in advance for all you help.
Sub ExporttoNewBook()
On Error GoTo cmdPublish_Click_ERR
Selection.Select
Dim wksht As Worksheet
'SAVE WORKBOOK TO STRING PROVIDED IN COVER SHEET NAMED RANGE
'PublishFile'
Dim PublishFile As String
PublishFile = Range("PublishFile").Value ' PublishFile is obviously a
named range
If Len(PublishFile) <> 0 Then
ThisWorkbook.Save
ThisWorkbook.SaveAs PublishFile
'PASTE SPECIAL TO REMOVE FORMULAS
For Each wksht In ThisWorkbook.Worksheets
wksht.Cells.Copy
wksht.Range("A1").PasteSpecial xlPasteValues
Next
'THIS IS WHERE I'M STUCK!!!!!! If there is another way, please adivse
' For Each wksht In ThisWorkbook.Worksheets
' With Selection
' If (wksht.Name = Selection) Then
' wksht.Cells.Copy
' wksht.Range("A1").PasteSpecial xlPasteValues
' End If
' End With
' Next
ThisWorkbook.Save
Else
MsgBox "Please supply a file name to publish as.", vbOKOnly +
vbCritical, "All Stop"
End If
Exit Sub
cmdPublish_Click_ERR:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical,
"All Stop"
End Sub