Loop command between 2 worksheets

[MissV]

I have got a situation in Excel which will most likely need to be solved by a Macro, and I would like some help because I have little VB experience.

I have two worksheets in an Excel workbook. Sheet1 has all the data (1998-2004) for a final report and Sheet2 has some data from a query (2003) that I need to compare to the data on Sheet1.

I need the macro to look at 1 column (Child/Parent), from Sheet2, if this combination is found in the same column in Sheet1 I need the entire row where the match was found on Sheet1 to be highlighted copied into sheet 3. I need this to loop through every record/row on sheet 1 until the row is blank. I then need it to move onto the next row in sheet 2 and repeat the above process.

More Clarification: Sheet1 contains 528 records, Sheet2 contains 115. If there is no match made, then do nothing to that row. So for Each Child/Parent on Sheet2, I need them matched and compared on Sheet1.

Am I making any sense? I appreciate your help immensely.

Kind Regards,
Vedica.

 

[LPlates]

This will show you (Using VB) how to open an Excel sheet(s) and get/set values to/from it/them

[blue]Option Explicit

Dim [/blue]Excel [blue]As Object
Dim [/blue]ExcelSheet [blue]As Object

Private Sub [/blue]Command1_Click()
[blue]Dim Name As String
Set [/blue]Excel = CreateObject("Excel.Application&quot
Excel.Visible = [blue]True
[/blue]Excel.WindowState = 2
Excel.Workbooks.[blue]Open [/blue]App.Path + "\expenses.xls"
[blue]Name [/blue]= Excel.ActiveWorkbook.Sheets(1).[blue]Name
Set [/blue]ExcelSheet = Excel.ActiveWorkbook.Sheets([blue]Name[/blue])
[blue]End Sub

Private Sub [/blue]Command2_Click()
Excel.Application.Cells(1, 1).Value = "Ahh, it changed!"
Text1 = Excel.Application.Cells(1, 3).Value
[blue]End Sub

Private Sub [/blue]Command3_Click()
ExcelSheet.SaveAs (App.Path & "\expenses2.xls&quot
[blue]End Sub

Private Sub [/blue]Form_Unload(Cancel [blue]As Integer[/blue])
[blue]Set [/blue]Excel = [blue]Nothing
Set [/blue]ExcelSheet = [blue]Nothing
End Sub
[/blue]

 

[MissV]

Thank you LPlates for your quick and kind response. However, I am still slightly unsure of the scripting as I am a beginner and have not yet fully grasped the concept.

For further clarification I have a workbook "ParentChildNew" and within it I have 2 worksheets;
1)"ClientVSLocation" [1998-2004 Jobs] 528 Records
2)"2003Jobs" [2003 Jobs Only] 115 Records

What I need to do is open "2003Jobs" and look at the first record:jobno field = column D and search every record in the "ClientVSLocation" to see if there are any matches on this jobno. If there are I need to copy the matched row and paste it into a new 3rd worksheet. I then need to repeat this process but for the next record in the "2003Jobs" worksheet.

Do you think you can help please?

Regards,

Vedica.

 

[LPlates]

Use a loop something like...

[blue]Dim [/blue]i [blue]As Integer
Dim [/blue]ii [blue]As Integer
Dim [/blue]x [blue]As Integer

For [/blue]i = 1 [blue]To [/blue]528
[blue]For [/blue]ii = 1 [blue]To [/blue]115
[blue]If [/blue]Excel.Application.Cells(1, i).Value = Excel.Application.Cells(1, ii).Value [blue]Then
[/blue]x = x + 1
[green]'3rd sheet add value (code below to create)
[/green]Excel.3rdSheet.Cells(1, x).Value
[blue]End If
Next [/blue]ii
[blue]Next [/blue]i

Code to create a excel sheet....

[blue]Private Sub [/blue]cmdCalculate_Click()

[blue]Set [/blue]objExcel = CreateObject("excel.application&quot [green]'Starts the Excel Session
[/green][blue]Set [/blue]objBook = objExcel.Workbooks.Add [green]'Add a Workbook
[/green][blue]Set [/blue]objSheet = objBook.Worksheets.Item(1) [green]'Select a Sheet
'objExcel.Application.Visible = False
[/green]objExcel.Application.Visible = [blue]True
[/blue]Rem Fill [blue]in [/blue]the row labels.
objExcel.Application.Cells(1, 1).Value = txt1st.Text
objExcel.Application.Cells(2, 1).Value = txt2nd.Text
objExcel.Application.Cells(3, 1).Value = txt3rd.Text
objExcel.Application.Cells(4, 1).Value = txt4th.Text
objExcel.Application.Cells(5, 1).Value = txt5th.Text
objExcel.Application.Cells(7, 1).Value = txt6th.Text
Rem Fill [blue]in [/blue]the values
objExcel.Application.Cells(1, 3).NumberFormat = "mm/dd/yy"
objExcel.Application.Cells(1, 3).Value = Text1.Text
objExcel.Application.Cells(2, 3).Value = Text2.Text
objExcel.Application.Cells(3, 3).Value = Text3.Text
objExcel.Application.Cells(4, 3).Value = Text4.Text
objExcel.Application.Cells(5, 3).Value = Text5.Text
objExcel.Application.Cells(7, 3).Formula = "=sum(c1:c5)"
objExcel.Application.Cells(7, 3).Font.Bold = [blue]True

[/blue]Text6.Text = objExcel.Application.Cells(7, 3).Value
[green]'objExcel.Application.Visible = True
'objExcel.Application.Visible = False

'objExcel.Columns("D:F&quot.ColumnWidth = 30
[/green]objExcel.Columns("A&quot.ColumnWidth = 20
[blue]End Sub
[/blue]

Im not going to do all the work for you but there is a good guideline for you

Good luck