Excel Button and new template

Codemanbuff

New Member
Joined
Mar 20, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
1647793580301.png

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:
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top