Snasui Excel Forum

Code: Select all

Sub Button_click()

Dim FName As String
Dim FPath As String
Dim sheet As String
Dim DOCno As Variant
Dim REV As Variant
Dim FG As String
Dim searching As String

    FG = Worksheets("SAVE AS").TextBox1.Value
    DOCno = Worksheets("SAVE AS").TextBox2.Value
    REV = Worksheets("SAVE AS").TextBox3.Value
   searching = Application.WorksheetFunction.VLookup(FG, Worksheets("DATA").Range("A:ZZ"), 1, False)

    FPath = "D:\"
    FName = DOCno & "_Rev." & REV & ".xlsx" '& Format(Date, "ddmmyy")

    With Sheets(searching).PageSetup 'ActiveSheet.PageSetup
        .LeftFooter = "CS-QC-CBA-" & DOCno & "/Rev." & REV '"Page &P"
        .CenterFooter = "Page:" & "&P/2"
        .RightFooter = "Quality Assurance Department"
    End With

    Sheets(searching).Copy
    ActiveSheet.Name = DOCno & "_" & REV
    ActiveWorkbook.SaveAs Filename:=FPath & FName

End Sub