Excel Button and new template

[Codemanbuff]

I'm a contractor and i use an excel workbook with different sheets (please see attached). i've been making more and more changes to it. i want to be a to click the PO button and have it create a new sheet from my "PO template" sheet in the workbook. i also want it to pull the contractor name into the new sheet and then pull the contractor address and other info from my "contractor-owner list". i also want to eventually do the same with the change order button but once i figure the change order button then i can copy that info.

Vendor: (test contractor)
Address:
City: State: Zip Code:
Office:

 

[Candyman8019]

You could try something like the following:

Sub CopyPOTemplate()
'Copy PO Template before sheet x
Sheets("PO Template").Copy Before:=Sheets(8)
'change tab name to Contractor Name
Sheets("PO Template (2)").Name = Sheets("PurchaseOrders").Range("B2").Value
'Set contractor name in new sheet. Do similar for address.
Sheets("Test Contractor").Range("A1") = Sheets("PurchaseOrders").Range("B2").Value

End Sub

 

[Codemanbuff]

This is kind of is working. it keeps telling me something is wrong code. i can get sheet to pop up if i hit the play button in the VBA(out of range Error) when i hit the button it doesnt work.
would it be possible for me to send you the file?

 

[Candyman8019]

sure. Can you put it in OneDrive or Dropbox and share a link

 

[Codemanbuff]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Candyman8019]

Give this a try:
Private Sub PO_Click()

Dim exists As Boolean
Dim tabtitle As String

tabtitle = ActiveCell.Value & ActiveCell.Offset(0, -1)

For i = 1 To Worksheets.Count
If Worksheets(i).Name = tabtitle Then
Worksheets(i).Activate
exists = True
End If
Next i

If Not exists Then
Sheets("PO Template").Range("B5").Value = ActiveCell.Value
Sheets("PO Template").Copy Before:=Sheets(8)
Sheets("PO Template (2)").Name = tabtitle
Sheets(tabtitle).Range("B6").Value = Application.WorksheetFunction.XLookup(Sheets(tabtitle).Range("B5").Value, Sheet8.Range("A2:A2000"), Sheet8.Range("B2:B2000"))
Sheets(tabtitle).Range("B7").Value = Application.WorksheetFunction.XLookup(Sheets(tabtitle).Range("B5").Value, Sheet8.Range("A2:A2000"), Sheet8.Range("c2:c2000"))
Sheets(tabtitle).Range("E7").Value = Application.WorksheetFunction.XLookup(Sheets(tabtitle).Range("B5").Value, Sheet8.Range("A2:A2000"), Sheet8.Range("d2:d2000"))
Sheets(tabtitle).Range("G7").Value = Application.WorksheetFunction.XLookup(Sheets(tabtitle).Range("B5").Value, Sheet8.Range("A2:A2000"), Sheet8.Range("E2:E2000"))
Sheets(tabtitle).Range("B8").Value = Application.WorksheetFunction.XLookup(Sheets(tabtitle).Range("B5").Value, Sheet8.Range("A2:A2000"), Sheet8.Range("f2:f2000"))
Sheets(tabtitle).Range("B9").Value = Application.WorksheetFunction.XLookup(Sheets(tabtitle).Range("B5").Value, Sheet8.Range("A2:A2000"), Sheet8.Range("g2:g2000"))

End If

End Sub