CODEWITHSUNDEEP
vbaexcel
L. Hicks
L. Hicks

Reputation: 3

Using "For Each" to select a cell on another sheet

I am creating a code that compares two different workbooks (a "master" and a "slave") and I want to copy any rows that were created in wb1 ("master) into the same spot in wb2 ("slave")

To do this I am using the following code as a part:

Sub CopySchedule()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyFrom As Range
Dim lRow As Long
Dim strSearch As String
Dim LastCell As Range
Dim ws As Integer
Dim MasterCell As Range
Dim GetRange As Range
Dim RowCount As Integer
Dim SlaveCell As Integer
Dim CheckN As Integer


Set wb1 = Application.Workbooks("Master Sheet.xlsx")
Set wb2 = ThisWorkbook

'Application.ScreenUpdating = False

For ws = 1 To wb1.Worksheets.Count

    Set ws1 = wb1.Worksheets(ws)
    Set ws2 = wb2.Worksheets(ws)

    With ws1

        RowCount = ws2.Cells(.Rows.Count, "A").End(xlUp).Row

        Set LastCell = .Cells(1, Columns.Count)

        For Each MasterCell In .Range("A4", LastCell)

            CheckN = 0

            For SlaveCell = 1 To RowCount

                If MasterCell.Value = ws2.Cells(SlaveCell, "A").Value Then
                    CheckN = 1
                    GoTo Line1
                End If

            Next SlaveCell

 Line1:
            Set GetRange = Range(MasterCell.Address(0, 0))
            MsgBox Range("GetRange").Address(0, 0)
            If CheckN = 1 Then
            Else
                ws2.Range("GetRange").EntireRow.Insert
                ws1.Range("GetRange").EntireRow.Copy Destination:=ws2.Range("GetRange")
            End If

        Next MasterCell

    End With

Next ws

The problem I am having is that once I have found a MasterCell value that is not in wb2, I need to insert a row into wb2 at the same range that the Mastercell is in wb1. I will then copy the row from wb1 to wb2.

The problem I am having is figuring out how to take "MasterCell", which is a range value that would look like 

wb1.Range("whatever cell it's on in the for loop")

and reference the same cell in wb2.

Does anyone have a solution?

Thank you!

Upvotes: 0

Views: 48

Answers (1)

teylyn
teylyn

Reputation: 35990

Your variable Mastercell is a range, with all the properties of a range. So you can extract the row number of the range with

myRow = Mastercell.row

and then use myRow to address the corresponding row in the other worksheet

            ws2.Range("A" & myRow).EntireRow.Insert
            ws1.Range("A" & myRow).EntireRow.Copy Destination:=ws2.Range("A" & myRow)

Upvotes: 0

Related Questions