Copy Cell Based on Another Cells value

[BFitz]

Hi all,

First of all, thank you for all the fantastic solutions that I've managed to borrow over the years!

I'm trying to create a Macro to move data from one tab to another based on the value in another cell.

Column G has various statuses in, based on whatever is in Column G, I want to copy the corresponding reference from Column B, to a second sheet. In the Second sheet, I have a few different sections.

For Example, 'New' would transfer to not Assigned, 'Awaiting info' & 'Assigned' would go to in progress etc.

To make it easier (and so I can adapt easier in future) I have Lookups finding all of the information based on the Reference.

Currently I have:

This currently cycles through and captures however many I set to "New" will then transfer into the relevant space on the 2nd sheet (Creating space for it to sit at the top first)

Easy View - is my 2nd sheet with the categories, and "Requests Master" is the main sheet where I want to move the data from.

Please let me know if you need more information, Hope someone out there can help me!

thanks,
Brad

 

[BFitz]

I've done some more tinkering with this and I've managed to make it so that it now copies the right amount of "new" cells from the first sheet,

this version for instance copies 4 times. However, this copies AG0001-AG0004 rather than 1-3 & 10.

Code is now

Sub TestMove()
'
' TestMove Macro
    Dim rngA As Range
    Dim cell As Range
    Dim A As Long
    Dim I As Integer
    
    
  A = 2
        Application.ScreenUpdating = False ' stop screen flicker
        
        Set rngA = Sheets("Requests Master").Range("G:G")
            For Each cell In rngA
              If cell.Value = "New" Then
                   
                   A = A + 1
                       
                        
                        Sheets("Easy View").Select
                        Range("C4:F4").Select
                        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                        Range("D5:F5").Select
                        Selection.Copy Destination:=Range("D4:F4")
                        
                        Sheets("Requests Master").Select
                        Sheets("Requests Master").Cells(A, 2).Copy Destination:=Sheets("Easy View").Range("C4")
                       
               
                     End If
       
            Next cell
            
            
            
    Sheets("Easy View").Select ' Go back to easy view
    Application.ScreenUpdating = True ' stop screen flicker
 
    
End Sub