Record data from one worksheet to another based on conditions

**sgp** · 12-13-2011, 08:02 AM

Hi all

I have macro button that records the data from one worksheet into another. My coding is in a total mess right now! I have the following worksheets:

1 - Input - The information is entered in the template

2 - View - The information is copied through from a macro button based on the order id and Category - I have attached an example which may not make sense but may give the idea to what i'm trying to achieve.

Thanks.

**Bob Phillips** · 12-13-2011, 08:51 AM

Sub Button4_Click()
Dim source As Worksheet
Dim cellCat As Range
Dim cellOrder As Range

    Set source = Worksheets("Input")
                   
    With Worksheets("View")
    
        Set cellCat = .Columns(1).Find(source.Range("B3").Value)
        If Not cellCat Is Nothing Then
        
            Set cellOrder = .Rows(2).Find(source.Range("B2").Value)
            If Not cellOrder Is Nothing Then
            
                .Cells(cellCat.Row, cellOrder.Column).Value = source.Range("B5").Value
                .Cells(cellCat.Row + 1, cellOrder.Column).Value = source.Range("B6").Value
                .Cells(cellCat.Row + 2, cellOrder.Column).Value = source.Range("B7").Value
            End If
        End If
    End With
End Sub

Haven't we already done this one?

**sgp** · 12-13-2011, 08:58 AM

Thanks Bob Phillips!

I did do something similar to this but it was through userforms.

**sgp** · 12-13-2011, 08:59 AM

Just a quick one - is there a way to inform the user that the data has been transfered? i.e. msgbox

Regards.

**arlu1201** · 12-13-2011, 08:52 AM

Should the data be copied to columns E, F & G of your View tab?

**Bob Phillips** · 12-13-2011, 09:04 AM

Sub Button4_Click()
Dim source As Worksheet
Dim cellCat As Range
Dim cellOrder As Range

    Set source = Worksheets("Input")
                   
    With Worksheets("View")
    
        Set cellCat = .Columns(1).Find(source.Range("B3").Value)
        If Not cellCat Is Nothing Then
        
            Set cellOrder = .Rows(2).Find(source.Range("B2").Value)
            If Not cellOrder Is Nothing Then
            
                .Cells(cellCat.Row, cellOrder.Column).Value = source.Range("B5").Value
                .Cells(cellCat.Row + 1, cellOrder.Column).Value = source.Range("B6").Value
                .Cells(cellCat.Row + 2, cellOrder.Column).Value = source.Range("B7").Value
                
                MsgBox "Data transferred for Category " _
                     & source.Range("B3").Value _
                     & ", Order #" & source.Range("B2").Value, vbOKOnly, "Data Transfer"
            End If
        End If
    End With
End Sub

**Marcol** · 12-13-2011, 09:11 AM

Never saw the replies, I'll post it for what it's worth.
Here's another way
Run this with your button

Sub TransferData()
    Dim RowNo As Long, ColNo As Long
    Dim rngRow As Range, rngCol As Range
    Dim wsInput As Worksheet, wsView As Worksheet
    
    Set wsInput = Sheets("Input")
    Set wsView = Sheets("View")
    
    If wsInput.Cells(2, "B") = "" Or wsInput.Cells(3, "B") = "" Then
        MsgBox "Incomplete Data", vbCritical
        Exit Sub
    End If
    
    On Error Resume Next
    With WorksheetFunction
        RowNo = .Match(wsInput.Range("B3"), wsView.Range("A:A"), 0)
        Err.Clear
        ColNo = .Match(wsInput.Range("B2"), wsView.Range("2:2"), 0)
        Err.Clear
    End With
    If RowNo = 0 Then
        MsgBox "Category not found", vbCritical
        Exit Sub
    End If
    If ColNo = 0 Then
        MsgBox "Order Number not found", vbCritical
        Exit Sub
    End If
    For n = 0 To 2
        wsView.Cells(RowNo + n, ColNo) = wsInput.Cells(5 + n, "B")
    Next
    MsgBox "Order Transferred", vbInformation
    
End Sub