Hiding sheet after copy same sheet into new workbook

[cbs81]

Hi all... I have a Workbook named "Reconciliation". In this workbook I have an Hidden worksheet named "revenue report" (which is protected), that I would like to save into a new workbook as a new filename as below.

The following code works, but how is it possible to REHIDE this HIDDEN WORKSHEET NAMED REVENUE REPORT in the source workbook named "Reconciliation".....

I tried my best buy cant find a solution....

I found that if the worksheet is hidden to begin with in the Workbook named "reconciliation" in order to copy it into another workbook you must first unhide it, otherwise there is an error message. I need this worksheet named "REVENUE REPORT" REHIDDEN in my Source workbook named "Reconciliation"..

I have come up with this so far but need to somehow go back to my original workbook and rehide this sheet. My previous workbook named Reconciliation is saved with the date and time(in seconds) in the filename:

i.e: reconciliation 120507 12.19.06

where the date and time are the current time that changes by the secont

IS there any solutions to this...

Any help from you geniouses is super appreciated...

thankyou



Sub Click()
'Dim DstFile As String 'Destination File Name
Dim op As String 'operator
op = Sheet38.Range("a12").Value
Dim Rr As String 'Revenue Report
Rr = Sheet38.Range("A13").Value
Dim pe As String 'For period ended xxxx
FD = Sheet38.Range("a14").Value
'Copy worksheet
Application.ScreenUpdating = False
Dim wb As Workbook
Dim NewShtName As String
NewShtName = "Revenue Report"
Worksheets("Revenue Report").Copy
Sheets("Revenue Report").Visible = True
Set wb = ActiveWorkbook
wb.Sheets("Revenue Report").Name = NewShtName
wb.SaveAs Application.ActiveWorkbook.Path & "\Revenue Report for " & _
op & " " & FD & " - Created " & Format(Date, "ddmmyy ") & _
Format(Time, "hh.mm.ss") & ".xls"
Application.ScreenUpdating = True
End Sub


Edited 2-May-2007 by geekgirlau. Reason: insert line breaks

[Bob Phillips]

When you copy a single worksheet like that, you create a workbook with just a single sheet. A workbook cannot have no visible sheets, so it won't work.

[cbs81]

Hi.. thankyou for your response, but its not the destination workbook I am trying to hide, its the source workbook, (ie, hiding the sheet named revenue report)

I have clarified what i am trying to do again as follows...


I have a Workbook named "Reconciliation" This is my source workbook. In this workbook I have an HIDDEN WORKSHEET named "revenue report" (which is protected), that I would like to save into a new workbook as a new filename named "Revenue Report XXXXXXXXXXXXX" as below.

The following code works, but how is it possible to REHIDE this HIDDEN WORKSHEET NAMED REVENUE REPORT in the source workbook named "Reconciliation" after I copy it across to the new workbook named Revenue report.....

This means, That there is 2 identical worksheets named "Revenue report" in 2 SEPARATE WORKBOOKS NAMED "RECONCILIATION" and "REVENUE REPORT"

IT is the source workbook named reconciliation that I am trying to re-hide after a copy is made accross to a new workbook named "revenue report" which contains this worksheet named "revenue report" ... Remember I said in my first paragraph that :

"I have a Workbook named "Reconciliation" This is my source workbook. In this workbook I have an HIDDEN WORKSHEET named "revenue report" (which is protected), "

I tried my best buy cant find a solution....

I found that if the worksheet is hidden to begin with in the Workbook named "reconciliation" in order to copy it into another workbook you must first unhide it, otherwise there is an error message. I need this worksheet named "REVENUE REPORT" REHIDDEN in my Source workbook named "Reconciliation"..

I have come up with this so far but need to somehow go back to my original workbook and rehide this sheet. My previous workbook named Reconciliation is saved with the date and time(in seconds) in the filename:
i.e: reconciliation 120507 12.19.06
where the date and time are the current time that changes by the secont
IS there any solutions to this...
Any help from you geniouses is super appreciated...
thankyou


Sub Click()
'Dim DstFile As String 'Destination File Name
Dim op As String 'operator

op = Sheet38.Range("a12").Value

Dim Rr As String 'Revenue Report

Rr = Sheet38.Range("A13").Value

Dim pe As String 'For period ended xxxx

FD = Sheet38.Range("a14").Value

'Copy worksheet
Application.ScreenUpdating = False

Dim wb As Workbook
Dim NewShtName As String

Set wb = ActiveWorkbook

NewShtName = "Revenue Report"
Worksheets("Revenue Report").Copy
Sheets("Revenue Report").Visible = True
wb.Sheets("Revenue Report").Visible = xlHidden

Set wb = ActiveWorkbook

wb.Sheets("Revenue Report").Name = NewShtName
wb.SaveAs Application.ActiveWorkbook.Path & "\Revenue Report for " & _
op & " " & FD & " - Created " & Format(Date, "ddmmyy ") & _
Format(Time, "hh.mm.ss") & ".xls"

Application.ScreenUpdating = True
End Sub


Edited 2-May-2007 by geekgirlau. Reason: insert line breaks

[geekgirlau]

Sub Click()
'Dim DstFile As String 'Destination File Name
Dim op As String 'operator
Dim Rr As String 'Revenue Report
Dim FD As String
Dim wb As Workbook
Dim NewShtName As String


op = Sheet38.Range("a12").Value
Rr = Sheet38.Range("A13").Value
FD = Sheet38.Range("a14").Value

'Copy worksheet
Application.ScreenUpdating = False

Set wb = ActiveWorkbook

NewShtName = "Revenue Report"
Worksheets("Revenue Report").Copy
Sheets("Revenue Report").Visible = True
wb.Sheets("Revenue Report").Visible = xlHidden

'you don't really need this line unless you are using the wb object
'later in your code - for "SaveAs" you can just use "ActiveWorkbook"
Set wb = ActiveWorkbook

' not required - inherits the name when you copy the sheet
'wb.Sheets("Revenue Report").Name = NewShtName
wb.SaveAs Application.ActiveWorkbook.Path & "\Revenue Report for " & _
op & " " & FD & " - Created " & Format(Date, "ddmmyy ") & _
Format(Time, "hh.mm.ss") & ".xls"

Application.ScreenUpdating = True
End Sub


A couple of quick points:

You really need to have Option Explicit set, which forces you to declare all variables. Your "FD" variable wasn't declared.
You'll notice I've moved all the variable declarations up the top - it's a good idea to keep these all at the start of your procedure so you can find them easily.
Make sure you put line breaks in your code - having to scroll for miles to the right is irritating both in the VBE window and here at VBAX. I've taken the liberty of inserting line breaks into the code you've posted above.

[lucas]

I closed your copy workbook too...
Option Explicit
Sub Click()
'Dim DstFile As String 'Destination File Name

Dim op As String 'operator
op = Sheet3.Range("a12").Value


Dim Rr As String 'Revenue Report
Rr = Sheet3.Range("A13").Value



Dim FD As String 'For period ended xxxx
FD = Sheet3.Range("a14").Value





'Copy worksheet
Application.ScreenUpdating = False
Dim wb As Workbook
Dim NewShtName As String
NewShtName = "Revenue Report"
' Worksheets("Sheet1").Visible = True
Worksheets("Revenue Report").Visible = True
Worksheets("Revenue Report").Copy




Set wb = ActiveWorkbook
wb.Sheets("Revenue Report").Name = NewShtName




wb.SaveAs Application.ActiveWorkbook.Path & "\Revenue Report for " & _
op & " " & FD & " - Created " & Format(Date, "ddmmyy ") & _
Format(Time, "hh.mm.ss") & ".xls"
ActiveWorkbook.Close True

Worksheets("Revenue Report").Visible = False
Application.ScreenUpdating = True


End Sub

[cbs81]

Hi there...

That worked excellently... now just another 2 things...

1) Instead of closing the copied workbook... how do you leave it open?? in the code from lucas

2) The location where the file is saved is on the root directory... whereas the source workbook is saved on another filename and directory... how do we get this file saved in a particular folder within my source workbook directory..

thankyou heaps

[lucas]

You want to leave the copy open and close the Reconciliation workbook?

I wondered if you were happy with the save to root...so you want it to save to the same directory as the Reconciliation workbook?

[cbs81]

Hi there Lucas,

1) You want to leave the copy open and close the Reconciliation workbook? I would prefer if they are both open, but if thats not possible, just close the Reconciliation workbook and have the copied workbook open

2) I wondered if you were happy with the save to root...so you want it to save to the same directory as the Reconciliation workbook?? I would really like to save to the same directory as the Reconciliation Workbook, BUT under a new folder in this same directory named, "Reconciliation". This file will be already created by the user in the same location as the reconciliation workbook..



Thanks mate

[lucas]

This should save it and keep both copies open....reconciliation will be the active workbook but the copy will still be open....the sheet is rehidden on reconciliation......give me a few minutes on the path.
Option Explicit
Sub Click()
'Dim DstFile As String 'Destination File Name

Dim op As String 'operator
op = Sheet3.Range("a12").Value


Dim Rr As String 'Revenue Report
Rr = Sheet3.Range("A13").Value



Dim FD As String 'For period ended xxxx
FD = Sheet3.Range("a14").Value





'Copy worksheet
Application.ScreenUpdating = False
Dim wb As Workbook
Dim NewShtName As String
NewShtName = "Revenue Report"
' Worksheets("Sheet1").Visible = True
Worksheets("Revenue Report").Visible = True
Worksheets("Revenue Report").Copy




Set wb = ActiveWorkbook
wb.Sheets("Revenue Report").Name = NewShtName




wb.SaveAs Application.ActiveWorkbook.Path & "\Revenue Report for " & _
op & " " & FD & " - Created " & Format(Date, "ddmmyy ") & _
Format(Time, "hh.mm.ss") & ".xls"


Application.Windows("Reconciliation.xls").Activate
' Workbook("Reconciliation").Activate
Worksheets("Revenue Report").Visible = False


Application.ScreenUpdating = True



End Sub

[lucas]

Try this in the saveas section .....the directory must exist...no error checking here.
wb.SaveAs Application.ThisWorkbook.Path & "\Reconciliation" & "\Revenue Report for " & _
op & " " & FD & " - Created " & Format(Date, "ddmmyy ") & _
Format(Time, "hh.mm.ss") & ".xls"

[cbs81]

Hi Lucas...

Small problem..... the workbook named "reconciliation" is not really named that because this is dynamically and automatically named for each operator and is tailored with the time of creation in the filename...

I think it would be easier to now CLOSE THIS RECONCILIATION original source workbook ... AND keep open the copied version which has just been created... Can this be done??

Thankyou for this

thankyou

[lucas]

Just put this line:
ActiveWorkbook.Close True
True tells it to save changes....change to false if you don't want to save changes.

put the above line just below this line in the code...should work.
Worksheets("Revenue Report").Visible = False

[cbs81]

your a genious... thankyou for your help..