Cycle and paste between workbooks with VBA

S

sjk1193

New Member
Joined
Nov 12, 2018
Messages
29
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)


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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Cycle through rows and copy VBA

I just need my code to only do the copy for when a specific column has a "yes" in it not just for rows 1 to 5. Not sure how I do that.


Code: 
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">InputFile.Sheets(1).Select
    ActiveSheet.Cells.Find(what:="Transaction Confirmation", SearchOrder:=xlByColumns).Select
    
    
    For i = 1 To 5
     If ActiveCell.Offset(i, 0) = "Yes" Then

                    Lastname = ActiveCell.Offset(i, -20)
                    Firstname = ActiveCell.Offset(i, -19)
                    InvEntityname = ActiveCell.Offset(i, -18)
                    Commitment = ActiveCell.Offset(i, -14)
                    Consultant = ActiveCell.Offset(i, -12)
               
             ThisWorkbook.Activate
                    Range("B24") = Firstname & " " & Lastname
                    Range("B13") = InvEntityname
                    Range("B14") = "C/O " & Firstname & " " & Lastname
                    Range("E41") = Commitment
                    Range("B31") = Consultant
               
                ActiveSheet.Name = Range("b13")
                ActiveSheet.Copy After:=ActiveSheet
                      
        InputFile.Activate
        
        End If
            
      Next</code>
 
Last edited by a moderator:
Upvote 0
Re: Cycle through rows and copy VBA

Cross posted https://www.excelforum.com/excel-pr...-all-the-code-in-between-vba.html#post5023353

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Also when posting code, please use code tags. The # icon in the reply window.
 
Upvote 0
Re: Cycle through rows and copy VBA

I'm assuming you did not write this script. And not sure what it's dong

So you want to look down a specified column and if "yes" is in this specified column copy this row to another sheet

I need to know what column to look down and copy this row to what sheet.
 
Upvote 0
Re: Cycle through rows and copy VBA

I wrote it but its taken me a lot of trial and error. I need to search column U for "yes" and if so save all of those variables like i have done
 
Upvote 0
Re: Cycle through rows and copy VBA

I will pass on this question since I see you have cross posted to other forum's
 
Upvote 0
Re: Cycle through rows and copy VBA

I posted on multiple forums because I'm trying to learn and correct myself wherever possible as fast as possible. I joined because i needed help and it doesnt help if you take the trouble to go through a post and choose not to answer if you can and then comment that you are passing as well
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
VBA Find match, if found, copy rows and paste in different workbook
Replies
6
Views
489
MARK858
MARK858
A
  • Question Question
VBA - Copy and Paste varying rows in Closed Workbook
Replies
9
Views
1K
mumps
mumps
A
  • Question Question
Change size when is pasting
Replies
2
Views
1K
ABF
A
R
  • Question Question
VBA copy from single sheet with cell values as reference and paste to another workbook's multiple tabs following the tabs name
Replies
4
Views
3K
GWteB
GWteB
J
  • Question Question
Loop through Data Validation list in Advanced Filter Copy Paste all Results on one sheet
Replies
1
Views
1K
jsepanski
J

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top