Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA to get details from sheet2

Posted on 2014-10-10
7
Medium Priority
?
585 Views
Last Modified: 2014-10-15
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
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
7 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40373677
looks confusing to me.
0
 

Author Comment

by:Jagwarman
ID: 40373928
and you're the expert how do you think I feel
0
 

Author Comment

by:Jagwarman
ID: 40373975
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Jagwarman
ID: 40373977
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 40381326
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
 

Author Comment

by:Jagwarman
ID: 40381671
ltlbearand that is perfect many thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question