How to modify the below code to include a loop or with statement (or any alternative method).

Thecraftycarrot

New Member
Joined
Nov 8, 2018
Messages
27
Hi all i have the below code that works perfectly when it is known that there is only one row item that needs to be copied from one sheet to another, then deleted from the orginal source. (delete in seperate code)

However i am struggling to convert the below code into a repeatable with / loop statment that checks, copies, moves, pastes and then deletes multiple rows of applicable data. (i.e. all those in s2 that match the critirea of s1 -e16)

Code:
Code:
Dim UpdateRow As Long
Dim UpdateCol As Long
Dim ColName As String
Dim S3 As Worksheet
Dim S2 As Worksheet
Dim S1 As Worksheet


Set S3 = Sheets("SHEET3")
Set S2 = Sheets("SHEET2")
Set S1 = Sheets("SHEET1")



        Rowname = S1.Range("E16").Value
        UpdateRow = S2.Range("A3:a1000").Find(Rowname, LookIn:=xlValues).Row
        ColName = "Contract number"
        UpdateCol = S2.Range("A2:AZ2").Find(ColName, LookIn:=xlValues).Column
        S2.Select
        S2.Range(Cells(UpdateRow, UpdateCol), Cells(UpdateRow, UpdateCol).Offset(0, 29).Address).Copy
        
        Rowname = S3.Range("a10000").End(xlUp).Row
        ColName = "Contract line number"
        UpdateCol = S3.Range("A2:AZ2").Find(ColName, LookIn:=xlValues).Column
        S3.Select
        S3.Cells(Rowname, UpdateCol).Paste


how would i modify / add to the above code so that it will cycle through or finds all rows with the applicable critirea, move into s3 and then delete each of the rows that match this critirea in s2

any insights would be great.

 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,108,633
Messages
5,523,995
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top