A little help (1 Viewer)

[hlmichel]

I am looking for a way to save a copy of a tab from a workbook to a separate file with VBA.

I can copy the tab to a seperate workbook or I can open a blank workbook and copy and paste the data to it. The problem is that I want values and formats only. I don't want any linked cells.

I have tried opening a new workbook, copying the data from the current book and then using paste special twice--data and then formats. The data is always pasted in the current spreadsheet even if I set the active book to the new book. I put in a message box that tells me which book is active for testing purposes.
Thank you for any help.

Henry L Michel

 

[shades]

Howdy. Can you post your code?
________
MERCURY BROUGHAM PICTURE

 

[hlmichel]

Thank you for the quick reply. I finally managed to get it to work after I realized that no matter what the active workbook was the information would be pasted in the workbook running the code. At least I think that was what was happening.

Here is the code that worked for me.
Private Sub CommandButton3_Click()
Dim name As String
Dim month As String
Dim year As String
Dim filename As String
Dim current As String

Application.DisplayAlerts = False
month = InputBox("Please enter the month of the report", "Export Report")
year = InputBox("Now please enter the year of the report", "Export Report")
Worksheets("riverside report").Range("c7") = month
name = "p:\riverside_" & month & "_" & year
temp = MsgBox("your report will be located at: " & name, vbOKOnly, "Export Report")
Worksheets("Riverside Report").Copy
current = ActiveWorkbook.name
Workbooks(current).Worksheets("riverside report").Range("A5:k19").Copy
Workbooks(current).Worksheets("riverside report").Range("A5").PasteSpecial (xlPasteValues)
Workbooks(current).Worksheets("riverside report").Range("A5").PasteSpecial (xlPasteFormats)
Workbooks(current).SaveAs (name)
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub