I'm at a standstill right now. I have some code that will run when cells in a Column A are selected. These particular cells are only for invoking the code. Each cell is used as a reference cell that is in the same row as the PO used. The code will create a second worksheet (invoice) and name the sheet by the PO.
I need to loop down the Timesheet to look for the same PO within a range and add the contents of the new row to the invoice template.
Each Row is a specific day of the week. So far, this is what I have:
I am very new at this, so any help would be great!
I need to loop down the Timesheet to look for the same PO within a range and add the contents of the new row to the invoice template.
Each Row is a specific day of the week. So far, this is what I have:
I am very new at this, so any help would be great!
Code:
'~~> Generate Field Ticket
Dim MySheet As String ' Client PO
MySheet = ActiveCell.Offset(0, 5).Text
Dim sDate As Variant 'Date that the work was performed
sDate = ActiveCell.Offset(0, 1).Value
Dim activePO As String
activePO = ActiveCell.Offset(0, 7).Text
If sDate <> 0 And IsDate(sDate) Then 'Make sure the date is entered correctly
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
If ws.Name = MySheet Then 'If a sheet was already generated with this Client PO, simply delete the sheet and generate an updated sheet
Application.DisplayAlerts = False
Sheets(MySheet).Delete
Application.DisplayAlerts = True
End If
Next 'If the Client PO has not been used yet, then generate a new sheet with the sheet name as the Client PO
Sheets("Template").Visible = True
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets("Template").Visible = False
ActiveSheet.Name = MySheet
Dim i As Integer
Dim lr As Long
lr = Sheet2.Cells(Sheet2.Rows.Count, "F").End(xlUp).Row
Dim SourcePO As String 'Client PO during loop
For i = 7 To lr 'Loop to last row of the timesheet that has been completed
SourcePO = Sheet2.Range("F" & i).Text
If IsEmpty(Sheet2.Range("A" & i).Value) Then
Exit Sub
ElseIf IsEmpty(SourcePO) Then
'==> go to the next row (Each day is it's own row. Some days below the current row could be blank, so the row needs to be skipped.)
End If
'===> Otherwise assign data to the invoice.
Next i
End If
End Sub
[IMG]https://www.mrexcel.com/forum/clear.gif[/IMG] Reply [IMG]https://www.mrexcel.com/forum/clear.gif[/IMG] Reply With Quote [IMG]https://www.mrexcel.com/forum/images/buttons/multiquote_40b.png[/IMG]
0 0