VBA to get details from sheet2

I have attached a file that hopefully will assist with my question.

I need to copy certain data from Sheet2 to Sheet1 but only where the cells in Column P equal -2 and there are two rules. 1) where there is a single Reference in column C in Sheet2 and 2) where there is a duplicate ref in column C

From Row11 in Sheet2

Only where there is a -2 in column P

If there is a single references in column C in Sheet2  then :
Populate column B in Sheet1 with the reference from Column C from Sheet2
Populate column C in Sheet1 with the data from Column T from Sheet2
Populate column D in Sheet1 with the data from Column Q from Sheet2
Populate column E in Sheet1 with the data from Column R from Sheet2
Populate column G with 0
Populate column J in Sheet1 with the data from Column U from Sheet2

If there are two references the same in column C in Sheet2 then [from the first Ref row in Sheet2]
Populate column B in Sheet1 with the reference from Column C from Sheet2
Populate column C in Sheet1 with the data from Column T from Sheet2
Populate column D in Sheet1 with the data from Column Q from Sheet2
Populate column E in Sheet1 with the data from Column R from Sheet2
Populate column G in Sheet1 with the data from Column Q from Sheet2
Populate column J in Sheet1 with the data from Column U from Sheet2

thank you in advance
JagwarmanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ltlbearand3Connect With a Mentor Commented:
OK.  I think I understand this, but it will get more complicated if my assumptions are incorrect.

Assumption 1:  All duplicates will be sorted together.  i.e. I only need to check the line below the current line to look for a duplicate.

Assumption 2:  That duplicate lines will always have the same value in Column P

If so, then you can try this code:

Public Sub CopyToSheet1()
    ' ExpertExchange Question ID 28535327
    ' http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28535327.html
    ' Expert: ltlbearand3 [http://www.experts-exchange.com/M_2469312.html]
    '
    Dim intStartRow As Integer
    Dim intEndRow As Integer
    Dim rngCurrent As Range
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    ' Because of the nature of your data, we have to be a little tricky in grabbing the data
    ' Set our starting Row as 11
    intStartRow = "11"
    
    ' Then find the last row of data
    ' Set Column and starting cell values
    Worksheets("Sheet2").Select
    intEndRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    ' Find the last used row on Sheet 1
    Set rngCurrent = Worksheets("Sheet1").UsedRange.End(xlDown).Offset(1)
    
    ' Loop Through Each row to look at the Data
    ' Using a For loop so we can skip the next line if a duplicate
    For i = intStartRow To intEndRow
        ' Check the Value of Column P
        If Range("P" & i).Value = -2 Then
            
            ' Copy Column C Sheet 2 to Column B Sheet 1
            rngCurrent.Value = Range("C" & i).Value
            
            ' Copy Column T Sheet 2 to Column C Sheet 1
            rngCurrent.Offset(ColumnOffset:=1).Value = Range("T" & i).Value
            
            ' Copy Column Q Sheet 2 to Column D Sheet 1
            rngCurrent.Offset(ColumnOffset:=2).Value = Range("Q" & i).Value
            
            ' Copy Column R Sheet 2 to Column E Sheet 1
            rngCurrent.Offset(ColumnOffset:=3).Value = Range("R" & i).Value
            
            ' Check if this is a duplicate
            ' And Copy the Correct Value to Column G in Sheet 1
            If Range("C" & i).Value = Range("C" & i + 1).Value Then
                rngCurrent.Offset(ColumnOffset:=5).Value = Range("Q" & i).Value
                
                ' Skip the next row
                i = i + 1
            Else
                rngCurrent.Offset(ColumnOffset:=5).Value = 0
            End If
            
            ' Copy Column U Sheet 2 to Column J in Sheet 1
            rngCurrent.Offset(ColumnOffset:=8).Value = Range("U" & i).Value
            
            ' Move to Next row on Sheet 1
            Set rngCurrent = rngCurrent.Offset(1)
        End If
    Next i
    
    Debug.Print "Done"
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
ProfessorJimJamCommented:
looks confusing to me.
0
 
JagwarmanAuthor Commented:
and you're the expert how do you think I feel
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JagwarmanAuthor Commented:
This may make it a little clearer I have attached a new file with Sheet1 completed

Also, with regards to the calculated field the calculation is not yet agreed with the user and I will add those later so they should not be part of the VBA

Many thanks
0
 
JagwarmanAuthor Commented:
0
 
JagwarmanAuthor Commented:
ltlbearand that is perfect many thanks
0
All Courses

From novice to tech pro — start learning today.