Hi,
I am slowly pulling together a tool to import work from customers emails, create the job sheets and email customer the "before" and "after" photo's upon completion. In this scenario I have rows of data and the Op's manager will use a drop down box to allocate the row (job) to a team (red, yellow, green etc)
red teams jobs are filtered to a worksheet named "red", Yellow to a worksheets named "yellow" and so on. Once allocated he can then click a command button at the end of the main sheets rows (one button per row) which will open the template jobsheet and copy relevant information from the Yellow / Red etc rows and populate the template, which it then saves as a new workbook named after a cell on the new jobsheet. I am having trouble with grabbing the cell value under the button (the team name) and using it to activate the correct worksheet.
Here is what I currently have:
Sub SaveJobsheets()
'
' SaveJobsheets Macro
'
'
Application.ScreenUpdating = False
Sheets("topleftcell").Select [COLOR=#008000]'this bit needs to get the value (which is the name of the worksheet the jobsheet details are on)'[/COLOR]
Rows("2:2").Select
Selection.Copy
ChDir "Z:\AVS\AVTS Development"
Workbooks.Open Filename:="Z:\AVS\AVTS Development\AVSworksheetTemplate.xlsx"
Sheets("ImportJob").Select
Range("A2").Select
ActiveSheet.Paste
Range("R2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("WorksOrder").Select
ActiveWindow.SmallScroll Down:=21
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1615340-1-VC002A-Yellow" 'This seems to be specific also which is
Range("B32").Select
ActiveWindow.SmallScroll Down:=-33
Range("A16").Select
ChDir "Z:\AVS\AVTS Development\WorksOrdersExported"
ActiveWorkbook.SaveAs Filename:= _
"Z:\AVS\AVTS Development\WorksOrdersExported\" & Range("B53") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close False
Worksheets("AVTS").Activate
Application.ScreenUpdating = True
End SubThanks for any help in advance.