Macro to copy data from one worksheet to another

[khelza]

Hey guys,

So I currently have a macro button that, when pressed, imports an entire table into sheet11 from a mySQL database.

In the same workbook, I have an excel sheet1, that has many of the same columns as the new table in sheet11. I would like to get it where when the macro button is pressed, it still pulls the whole table into sheet11, but then copies rows from sheet11, into sheet1 if the ID matches (found in column A of both sheets).

It would be nice to get it where it matches CW_ID (Sheet11:A) with CW_ID (Sheet1:A), then..
Description(Sheet11:B) get filled into Description (Sheet1:M)
Manufacturer(Sheet11:C) get filled into Manufacturer (Sheet1:O)
Model Number (Sheet11:D) get filled into Manufacturer (Sheet1:P)
etc..

sheet11:

Sheet1:

I have looked at so many examples on the web, but none that work for me.

What I have so far:

Private Sub CommandButton21_Click()

' Create a recordset object.
Dim rsMaterialsdb As ADODB.Recordset
Dim Lcw_ID As String
Dim LRow As Integer
Dim LFound As Boolean
Set rsMaterialsdb = New ADODB.Recordset


'connect to your mysql server
ADOExcelSQLServer


With rsMaterialsdb
    ' Assign the Connection object.
    .ActiveConnection = cn
    ' Extract the required records.
    .Open "SELECT m.CW_id,m.Description,ma.Manufacturer, m.Model_Number, pv.vendor AS Primary_Vendor, av.vendor AS Alternate_Vendor,m.Cost_CND FROM materials m INNER JOIN manufacturers ma ON m.Manufacturer=ma.Manuf_ID INNER JOIN vendors pv ON pv.Vendor_ID=m.primary_vendor INNER JOIN vendors av ON av.Vendor_ID=m.alternate_vendor ORDER BY m.CW_ID"
    ' Copy the records into cell O6 on Sheet1.
    Sheet11.Range("A2").CopyFromRecordset rsMaterialsdb
    
    ' Tidy up
    .Close
End With


cn.Close    'close connect to db

'*The above code works: pulls table from mySQL into sheet11. The following code is supposed to match ID's in column A of both sheets, and copy data to sheet1


'Retrieve cw_ID value to search for
Lcw_ID = Sheet11.Range("A2").Value
   
Sheet1.Select
   
'Start at Row 2 (Headers are on row 1, data starts at 2)
LRow = 2
LFound = False
   
   While LFound = False
   
      'Encountered blank cell in column A, terminate search
      If Len(Cells(LRow, 1)) = 0 Then
         MsgBox "No matching cw_ID was found."
         Exit Sub
         
      'Found match in row 1
      ElseIf Cells(LRow, 1) = Lcw_ID Then
      
         'Select values to copy from sheet11
         Sheet11.Select
         Range("B2:G55").Select  'Not sure how to use a variable range, this line gives me errors anyways
         Selection.Copy
         
         'Paste onto sheet1
         Sheet1.Select
         Cells(2, 13).Select     'again, not sure how to make this a variable range
         Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
         False, Transpose:=False
         
         LFound = True
         MsgBox "The data has been successfully copied."
         
      'Continue searching
      Else
         LRow = LRow + 1
      End If
      
   Wend
                
End Sub

Any help would be greatly appreciated!

 

[khelza]

This worked! Thank you so much :D Much appreciated

 

[mumps]

My pleasure.