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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
495
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 20, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 2, 2020
Messages
495
Office Version
  1. 365
Platform
  1. Windows
sure. Can you put it in OneDrive or Dropbox and share a link
 

Codemanbuff

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

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
495
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,175,528
Messages
5,897,950
Members
434,688
Latest member
vi28

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
Top