Updating an excel sheet with info from another workbook

Question

Let me start off by saying I am very new to working with excel and VBA, but do have some experience with c++.

The situation:

I'm trying to update one sheet with data found in another workbook. The source file is organized in such a way that each new work ticket is given a column. As more tickets come in, more columns are created, and various information about that ticket is listed vertically.

Basically what I'm trying to do is keep a second file updated with the same ticket numbers as the first, but with a different formatting:

Basic example of the two sheets

Here's what I have so far, although very rough for a basic idea of what I'd like the code to do:

Sub Update_Click() //Button to update destination file

Workbooks.open("C:\Documents\mysourcefile.xlsm")
dim i,j as integer
i=4 //starting column of source file where first ticket is stored
j=2 //starting column of destination file where first ticket is stored

while worksheets("mysourcesheet").Value(i,2)<>0 //all work has customer, but
                                                //may not have a ticket 
                                                //number

if Worksheets("mysourcesheet").value(i,1) = 0 Then

i=i+1          //some columns in the source are blank due to canceled orders
               //this is to go to the next column 

else
if Worksheets("mysourcesheet").value(i,1)=Worksheets("mydestsheet").value(j,1)
then
i=i+1
j-j+2      //go onto the next if already updated
           //J+2 to account for formatting of the cells

Else
Worksheets("mysourcesheet").value(i,1)=Worksheets("mydestsheet").value(j,1)
Worksheets("mysourcesheet").value(i,2)=Worksheets("mydestsheet").value(j,2)
Worksheets("mysourcesheet").value(i,3)=Worksheets("mydestsheet").value(j,4)
Worksheets("mysourcesheet").value(i,4)=Worksheets("mydestsheet").value(j,5)

//copy the data

i=i+1
j=j+2

end if
end if
end sub

I realize this is probably riddled with mistakes/fundamental errors, but if anybody can lend a hand that would be great!

Answer 1

This will copy new tickets if the customer is not empty, from 1 column in source into 2 columns

Private Sub Update_Click()
    Dim wb As Workbook, ur1 As Range, ur2 As Range, i As Long
    Dim fr1 As Long, fr2 As Long, lr1 As Long, lr2 As Long, lc1 As Long, lc2 As Long

    Application.ScreenUpdating = False

    Set wb = Workbooks.Open("E:\mysourcefile.xlsm")
    Set ur1 = Me.UsedRange
    Set ur2 = wb.Worksheets("mysourcesheet").UsedRange

    fr1 = ur1.Row: lr1 = fr1 + (ur1.Rows.Count - 1) - 1
    fr2 = ur2.Row: lr2 = fr2 + (ur2.Rows.Count - 1) - 1
    lc1 = ur1.Column + ur1.Columns.Count - 2:   lc2 = ur2.Column + ur2.Columns.Count - 1

    If Len(ur2.Cells(fr1 + 1, lc2)) > 0 Then                       'customer not empty
        If ur1.Cells(fr1 + 1, lc1) <> ur2.Cells(fr1 + 1, lc2) Then 'if last cutomer differ
            With ur1
              .Cells(fr1 + 0, lc1 + 2) = ur2.Cells(fr2 + 0, lc2)
              .Cells(fr1 + 1, lc1 + 2) = ur2.Cells(fr2 + 1, lc2)
              .Range(.Cells(fr1 + 0, lc1 + 2), .Cells(fr1 + 0, lc1 + 3)).MergeCells = True
              .Range(.Cells(fr1 + 1, lc1 + 2), .Cells(fr1 + 1, lc1 + 3)).MergeCells = True
              .Cells(fr1 + 2, lc1 + 2) = "Target"
              .Cells(fr1 + 2, lc1 + 3) = "Actual"
              .Cells(fr1 + 2, lc1 + 2).ColumnWidth = .Cells(fr1 + 2, lc1).ColumnWidth
              .Cells(fr1 + 2, lc1 + 3).ColumnWidth = .Cells(fr1 + 2, lc1).ColumnWidth
    .Range(.Cells(fr1, lc1 + 2), .Cells(fr1 + 2, lc1 + 3)).HorizontalAlignment = xlCenter
    .Range(.Cells(fr1, lc1 + 2), .Cells(lr1 + 1, lc1 + 3)).Borders.Weight = xlThin
              For i = fr1 + 3 To lr1 + 1
                  .Cells(i, lc1 + 2) = Now  'Target date
                  .Cells(i, lc1 + 3) = ur2.Cells(i - 1, lc2)
              Next
            End With
        End If
    End If
    wb.Close False
    Application.ScreenUpdating = True
End Sub

---