Cycle and paste between workbooks with VBA

[sjk1193]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Lastname[/TD]
[TD]Firstname[/TD]
[TD]InvEntityname[/TD]
[TD]xxx[/TD]
[TD]Commitment[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]InvoiceAmount[/TD]
[TD]xxx[/TD]
[TD]Y/N[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]def[/TD]
[TD]sddfgdfg[/TD]
[TD][/TD]
[TD]3513[/TD]
[TD][/TD]
[TD][/TD]
[TD]54[/TD]
[TD][/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]asd[/TD]
[TD]dfs[/TD]
[TD]dfgdfgs[/TD]
[TD][/TD]
[TD]543[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]g[/TD]
[TD]dfgd[/TD]
[TD][/TD]
[TD]354[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]asdf[/TD]
[TD]rtyr[/TD]
[TD]dfgdf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sdfg[/TD]
[TD]dfg[/TD]
[TD][/TD]
[TD][/TD]
[TD]453[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dfg[/TD]
[TD]qyh[/TD]
[TD][/TD]
[TD][/TD]
[TD]354[/TD]
[TD][/TD]
[TD][/TD]
[TD]123[/TD]
[TD][/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]plm[/TD]
[TD]oth[/TD]
[TD]fgh[/TD]
[TD][/TD]
[TD]953[/TD]
[TD][/TD]
[TD][/TD]
[TD]123[/TD]
[TD][/TD]
[TD]yes[/TD]
[/TR]
</tbody>[/TABLE]

this is the code i have so far... not sure how to continue ( all the offsets are right for my spreadsheet currently)

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

 

[Fluff]

Re: Cycle through rows and copy VBA

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).

I have merged your two threads.
Please take the time to read the rules, Thanks