(VBA) Create multiple Invoices based off information from a Master Timesheet

[dcoker]

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!

'~~> 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
[COLOR=#ff0000]    For i = 7 To lr [/COLOR][COLOR=#008000]'Loop to last row of the timesheet that has been completed[/COLOR][COLOR=#ff0000]
        SourcePO = Sheet2.Range("F" & i).Text
        
        If IsEmpty(Sheet2.Range("A" & i).Value) Then
            Exit Sub
            
        ElseIf IsEmpty(SourcePO) Then
        [/COLOR][COLOR=#008000]'==> 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.)[/COLOR][COLOR=#ff0000]
        End If
[/COLOR][COLOR=#008000]
'===> Otherwise assign data to the invoice.
[/COLOR]
Next i



End If
End Sub

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.



There you explain with examples and colors what data to take and where you want to put them

 

[dcoker]

https://www.dropbox.com/s/cer124atxdbfsak/Timesheet Template.xlsm?dl=0

 

[DanteAmor]

But the sheets are empty, you must also explain a step-by-step example, what data from column A I should select, what data you want to copy and where exactly you will paste it.
If all the above you put it in the file and explain it, it will be easier to understand.
Do not worry about the code, the important thing is that you capture what you need.

 

[dcoker]

I appreciate it. I will be replying soon with an updated file. Thank you!

 

[DanteAmor]

There is no problem, I gladly review it when you have the file.

 

[dcoker]

Here is a rough example. I hope to update more when I am not traveling. I hope this example will give you an idea. The 'Generate Ticket' Cell in each row of column A will create an invoice using the appropriate template based on the date of that row. I now just need to run through the list to check for the same client po and add to the invoice already created from the first row of data, starting at row 15.

https://www.dropbox.com/s/cer124atxdbfsak/Timesheet Template.xlsm?dl=0

 

[dcoker]

There is no problem, I gladly review it when you have the file.

Dante,

Did the file work for you?

 

[DanteAmor]

Sorry, I did not receive the notification, I'll check it tomorrow.

 

[DanteAmor]

But the sheets are empty, you must also explain a step-by-step example, what data from column A I should select, what data you want to copy and where exactly you will paste it.
If all the above you put it in the file and explain it, it will be easier to understand.
Do not worry about the code, the important thing is that you capture what you need.

You have to explain to me with data your example of what you want to pass from one sheet to another. Just telling me to pass the data in row 15 does not tell me anything. You have to be more specific in the example and use the data of your sheet.