ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,785
- Office Version
- 2007
- Platform
- Windows
Morning,
I use the code below to allow the user to add any extra info on my database before printing the final copy.
The line in Red is where im stuck.
Currently the sheet name "INV" is my problem, Yes it works fine if the sheet will ALWAYS be called INV but this isnt the case
The sheet can be any name as its a copy from the original, So one minute its TOM JONES then it could be BRUCE SMITH
Can you advise what / how i should name it as it cant be a fixed name, it needs to be flexible
If it help this code is run from the sheet in question at the time THEN the user is taken to my database to make the edit
I use the code below to allow the user to add any extra info on my database before printing the final copy.
The line in Red is where im stuck.
Currently the sheet name "INV" is my problem, Yes it works fine if the sheet will ALWAYS be called INV but this isnt the case
The sheet can be any name as its a copy from the original, So one minute its TOM JONES then it could be BRUCE SMITH
Can you advise what / how i should name it as it cant be a fixed name, it needs to be flexible
If it help this code is run from the sheet in question at the time THEN the user is taken to my database to make the edit
Rich (BB code):
Private Sub PrintGeneratedSheet_Click()
Dim rng As Range
Dim answer As Integer
answer = MsgBox("ANY INFO NEED ADDING TO INVOICE LIKE VIN ETC ?" & vbNewLine & vbNewLine & "BEFORE PRINTING ??", vbInformation + vbYesNo, "ANY INFO PRINT OK MESSAGE")
If answer = vbNo Then
ActiveWindow.SelectedSheets.PrintOut copies:=1
Remove_Sht ' THIS WILL DELETE THE GENERATED WORKSHEET THEN ONCE DELETED RETURN TO WORKSHEET INV
Else
MsgBox "ADD THE EXTRA INFO THEN PRINT MY COPY"
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
End If
End Sub