So What I need to do is copy and paste the values from the green columns in workbook 1 into workbook 2 if its says "yes" in the last column. I then need to cycle to the next row in workbook 1 and paste the values into a NEW worksheet in workbook 2 and do the same until it no longer says yes in the last column of workbook 1
example. I would copy all the bold values from the first row and paste them into workbook 2 insert a new worksheet and then loop
Workbook 1
<tbody>
</tbody>
this is the code i have so far... not sure how to continue ( all the offsets are right for my spreadsheet currently)
example. I would copy all the bold values from the first row and paste them into workbook 2 insert a new worksheet and then loop
Workbook 1
| Lastname | Firstname | InvEntityname | xxx | Commitment | xxx | xxx | InvoiceAmount | xxx | Y/N |
| abc | def | sddfgdfg | 3513 | 54 | yes | ||||
| asd | dfs | dfgdfgs | 543 | 12 | yes | ||||
| d | g | dfgd | 354 | 12 | yes | ||||
| asdf | rtyr | dfgdf | |||||||
| sdfg | dfg | 453 | |||||||
| 23 | |||||||||
| dfg | qyh | 354 | 123 | yes | |||||
| plm | oth | fgh | 953 | 123 | yes |
<tbody>
</tbody>
this is the code i have so far... not sure how to continue ( all the offsets are right for my spreadsheet currently)
Code:
Dim InputFile As Workbook
Dim OutputFile As Workbook
'other code here not relevant
Set InputFile = Workbooks.Open(filepath)
Set OutputFile = ThisWorkbook
Dim Lastname As String
Dim Firstname As String
Dim InvEntityname As String
Dim Commitment As Long
Dim InvoiceAmount As Long
InputFile.Sheets(1).Select
ActiveSheet.Cells.Find(what:="Last Name", SearchOrder:=xlByColumns).Select
Lastname = ActiveCell.Offset(1, 0)
Firstname = ActiveCell.Offset(1, 1)
InvEntityname = ActiveCell.Offset(1, 2)
Commitment = ActiveCell.Offset(1, 6)
InvoiceAmount = ActiveCell.Offset(1, 15)
ThisWorkbook.Sheets(1).Activate
Range("c24") = Lastname
Range("D24") = Firstname
Range("B13") = InvEntityname
Range("E41") = Commitment
Range("G41") = InvoiceAmount
ActiveSheet.Name = Range("b13")
Sheets.Add After:=ActiveSheet
