Copy worksheet but the new worksheet to omit some items

[ipbr21054]

Hi,
I have a worksheet which has some command buttons on etc.
In the code used below i copy this worksheet to create another worksheet where the customers name is used for the sheet name,ALL COMMAND BUTTONS ARE ALSO COPIED OVER.
Once the job is complete the user will go to the sheet which has the relevant customers name on it & run the command button for which it prints & deletes it.

The above works fine BUT sometimes the user will press a command button that they do not need to.
The only button they should press is the one that prints then deletes it,simples ha ha ha

I aim to hide all the command buttons that do not need to be on the copied worksheet & thus just showing the one used for printing / deleting.

Using the code like Command1.Visible = False works fine.
So i just did this for all the items in question But it hid all the items on the main worksheet & not the copied worksheet.

Please advise how to do this,bearing in mind each copied worksheet is name after the customer.

Not sure if i should be using With sheets "CUSTOMERS NAME" because dont know how to code it if customers name will change every time

Private Sub Generate_Pdf_Click()
  Dim answer As Integer
  Dim sPath As String, strFileName As String
  Dim wks As Worksheet
  Set wks = ActiveSheet
  
  
  With ActiveSheet
  If Range("G13") = "" Then
    MsgBox "NO NAME SELECTED IN THE CUSTOMER DETAILS SECTION", vbCritical, "NO CUSTOMER SELECTED MESSAGE"
    Range("G13").Select 'CHECKING IF CUSTOMER IS SELECTED
  Exit Sub
  End If
  
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select 'CHECKING IF PAYMENT TYPE HAS BEEN SELECTED
  Exit Sub
  End If
  
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  With ActiveSheet
      .ExportAsFixedFormat Type:=xlTypePDF, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
  End With 'CURRENT INVOICE IS NOW SAVED
   
  With Sheets("DATABASE")
      Worksheets("DATABASE").Activate
  End With

  Set rng = ActiveSheet.Columns("A:A")
    findString = Worksheets("INV").Range("G13").Value
  Set cell = rng.Find(What:=findString, LookIn:=xlFormulas, _
    LookAt:=xlWhole, MatchCase:=False) ' CUSTOMER FOUND IN COLUMN A
   
  If cell Is Nothing Then
    MsgBox "NO CUSTOMER WAS FOUND"
  Else
  With Sheets("DATABASE")
    cell.Select
    ActiveCell.Offset(0, 15).Select ' CUSTOMERS CELL IN COLUMN P NOW SELECTED
  End With
  End If
    
  If Len(ActiveCell.Value) <> 0 Then
       ValueInInvoiceCell.Show 'MESSAGE SHOWN IF CUSTOMERS INVOICE CELL IN COLUMN P HAS A VALUE IN IT

  Exit Sub
  Else
       TransferInvoiceNumber.Show 'NOW ENTER INVOICE NUMBER IN CUSTOMERS CELL IN COLUMN P & NOW HYPERLINKED
  End If
    
    With Sheets("DATABASE")
      Worksheets("INV").Activate 'WORKSHEET INVOICE HAS NOW BEEN ACTIVATED
      End With
  With ActiveSheet
  MsgBox "PRINTING DISIBLED"
      'ActiveWindow.SelectedSheets.PrintOut copies:=1
  End With

  
  ActiveSheet.Copy After:=Worksheets(Sheets.Count)
  If wks.Range("G13").Value <> "" Then
  On Error Resume Next
  ActiveSheet.NAME = wks.Range("G13").Value 'NEW COPY WORKSHEET NOW CREATED
  End If
  End With
    
  wks.Activate
     Range("L4").Value = Range("L4").Value + 1 'INVOICE IS INCREMATED BY 1
     Range("G27:L36").ClearContents   'WORKSHEET DETAILS NOW CLEARED
     Range("G46:G50").ClearContents
     Range("L18").ClearContents
     Range("G13").ClearContents
     Range("G13").Select
     ActiveWorkbook.Save

  Call PasteIfFormulas_Click
  ActiveWorkbook.Save
  
  
  End Sub

 

[ipbr21054]

Just an update.
No matter what i do the code applies it to the original & not the copied sheet.

I have used F8 & stepped though the code watching for when the copied sheet is active.
I then added my code at that point to enter 999 in cell G1
I was expecting to see 999 in cell G1 of the copied sheet but it was placed in the cell G1 on the original.

At this point shown below the copied sheet is active so how do i apply my code please.
I think my main reason is not being able to call / select to copied code as its name is always changing.
If i knew who to call / select that sheet once the generate pdf code has finished then i can advise it to hide the command buttons

  ActiveSheet.Copy After:=Worksheets(Sheets.Count)
  If wks.Range("G13").Value <> "" Then
  On Error Resume Next
  ActiveSheet.NAME = wks.Range("G13").Value 'NEW COPY WORKSHEET NOW CREATED
  End If
  End With

 

[ipbr21054]

My last try.

I added the code in RED to select the last worksheet, of which it does no problem.

So i then added the code in BLUE to see if 999 would be placed in the cell at G1 on the selected sheet. "if so i would add the correct code"
Even though the last worksheet was selected 999 is added to the original sheet & NOT this sheet i just selected.

I will await help please.

  wks.Activate
     Range("L4").Value = Range("L4").Value + 1 'INVOICE IS INCREMATED BY 1
     Range("G27:L36").ClearContents   'WORKSHEET DETAILS NOW CLEARED
     Range("G46:G50").ClearContents
     Range("L18").ClearContents
     Range("G13").ClearContents
     Range("G13").Select
     ActiveWorkbook.Save

  Call PasteIfFormulas_Click
       With Workbooks("TEST.xlsm").Worksheets(Worksheets.Count)
            Range("G1").Value = 999
       End With
   
  
  End Sub

 

[Tupe77]

I didn't get to test your code, so my instruction is only general and you have to apply it yourself.

Using an active sheet everywhere greatly increases the likelihood of these types of problems. Avoid it!

Sub newtest()
Dim wks As Worksheet
Set wks = ActiveSheet  
Dim wksNew As Worksheet

  
  If wks.Range("G13").Value <> "" Then                  ' if name
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)    ' Copy to last
    ActiveSheet.Name = wks.Range("G13").Value           ' rename sheet
    Set wksNew = ActiveSheet                            ' set wksNew as new sheet
  End If

    With Workbooks("TEST.xlsm").Worksheets(Worksheets.Count)
         .Range("G1").Value = 999 ' With requires a dot before Range.
    End With       
       
' With these examples, you can print sheet names in the Immediate window. (Copy to the point where the wrong sheet is selected.)
Debug.Print ActiveSheet.Name
Debug.Print Sheets(Sheets.Count).Name
Debug.Print wks.Name
Debug.Print wksNew.Name

End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[ipbr21054]

Thanks I will try that tomorrow.

The value 990 was just to see if I had activated the sheet correctly & was able to add the 999

Assuming the above did work for me then the 999 code was going to be deleted & I was going to add code for various command buttons that I needed to be hidden.

Example.

Commandbutton1.Visible = False
Commandbutton4.Visible = False

This would then eliminate the option of the user selecting the wrong button.
So I only want to show the command button that they needed to press.

Today I have spent at least 4 hours trying to solve this but no matter what I tried even as simple as Msgbox”Hi” would always be on the original sheet & not the copied sheet.

Hope this works.
Thanks.

 

[Tupe77]

Did you mean something like this??

Sub HideButtons()

' Define the original sheet
Dim wks As Worksheet
Set wks = ActiveSheet
Debug.Print wks.Name


' Define the copied sheet
If wks.Range("G13").Value <> "" Then                    ' if name
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)    ' Copy to last sheets
    ActiveSheet.Name = wks.Range("G13").Value           ' rename sheet
    Dim wksNew As Worksheet
    Set wksNew = Worksheets(wks.Range("G13").Value)     ' set wksNew as new sheet (Selected by name: I would recommend using this)
    Debug.Print wksNew.Name
End If
       
' Hide buttons
With wksNew
    .Shapes("CommandButton1").Visible = False
    .Shapes("CommandButton4").Visible = False
End With

End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[ipbr21054]

I have uploaded a file for you to use.

DOWNLOAD THIS FILE

You will see the sheets supplied are DATABASE & INV

So do this,
Sheet INV cell G13 use drop down arrow & select A1
Top left click GENERATE pdf
With the pop up message just click it
Click OK to PRINTING IS DISABLED.

Now the code has created another sheet using the name that was selected in cell G13 thus being A1

So click on sheet A1
Now you will see various color command buttons.

I wish to only see PRINT & DELETE COPY INCE JOB DONE

Thats it.

Whatever i do is somehow being applied to sheet DATABASE

I tried your two codes above but it still didnt hide the command buttons.

 

[ipbr21054]

Anybody able or see why I can’t hide the command buttons on the correct sheet.

A day of playing with it to so far get nowhere.

 

[Tupe77]

There are about 80 vba modules of code in your workbook. Lots of automatic events and broken references outside the workbook.
So I can't try the functionalities of your workbook.

The following code copies the INV sheet and names the copy according to the value in cell G13.
Then it hides Shapes other than PrintGeneratedSheet from the new sheet.

I recommend copying it as it is and trying it alone. When it works, then you edit it into your own code.

I tested it without your vba code and I think it worked correctly.

I added the TurnOffFeatures function so that worksheet_change and other events would not interrupt the execution of the code when you test it.

Option Explicit

Sub HideButtons()
On Error GoTo ErrHand:
Call TurnOffFeatures ' Stop events etc...

' Define the original sheet
Dim wks As Worksheet
Set wks = Worksheets("INV")

' Define the copied sheet
If wks.Range("G13").Value <> "" Then                    ' if name
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)    ' Copy to last sheets
    Worksheets(Sheets.Count).Name = wks.Range("G13").Value           ' rename sheet
    Dim wksNew As Worksheet
    Set wksNew = Worksheets(wks.Range("G13").Value)     ' set wksNew as new sheet (Selected by name: I would recommend using this)
End If

' Hide all others CommanButtons except "PrintGeneratedSheet"
Dim Shape As Shape
For Each Shape In wksNew.shapes
    If Shape.Name <> "PrintGeneratedSheet" Then
        Shape.Visible = False
    End If
Next

ErrHand:
    Call TurnOnFeatures ' Enable events etc...
End Sub

Public Function TurnOffFeatures() ' Stop events etc...
Application.Calculation = xlManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
End Function

Public Function TurnOnFeatures() ' Enable events etc...
Application.Calculation = xlAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
End Function

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[ipbr21054]

The broken parts you mention are for the sheets that I deleted so I could share this file.

Will check the code now