Macro to insert data from another sheet stops working
 

Hello,

I want to thank everyone for their help on this in advance!

I am currently working on a budget program in which I have a module
called InsertAdjustments. I call the module to insert adjustments from
a worksheet named Manual Adjustments which populates each sheet.
Unfortunately the code works for some sheets, but not others. It even
stops half way through one of the sheets. I have been looking on this
site to find some answers but haven't found anything yet.

Please help! My code was based off of a posting by "Dave". Here's
the code I'm currently using:

Sub InsertAdjustments()

Const cMasterKeyColumn = 3 'column number used for comparison
Const cUpdateKeyColumn = 3 'column number used for comparison

Dim wsMaster As Worksheet 'destination worksheet
Dim wsUpdate As Worksheet 'local worksheet
Dim rngMasterSearchRange As Range 'rng in master to search
Dim rngFindResult As Range 'rng in local to find
Dim lMasterLastRow As Long 'last row when appending in master
Dim lastrow As Long
Dim lUpdateRow As Long 'row to update copied from local sheet

Set wsMaster = ThisWorkbook.Worksheets("ManualAdjustments")
Set wsUpdate = ThisWorkbook.ActiveSheet
lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp)(2).Row
Set rngMasterSearchRange = wsMaster.Range("C2:C" & lastrow)
lUpdateRow = 8

Do While wsUpdate.Cells(lUpdateRow, cUpdateKeyColumn).Value < ""
Set rngFindResult = rngMasterSearchRange.Find( _
What:=wsUpdate.Cells(lUpdateRow, cUpdateKeyColumn).Value, _
LookIn:=xlValues, _
MatchCase:=False)

If rngFindResult Is Nothing Then
Else 'value found, so update values
wsUpdate.Cells(lUpdateRow, 6).Value =
wsMaster.Cells(rngFindResult.Row, 6).Value
wsUpdate.Cells(lUpdateRow, 7).Value =
wsMaster.Cells(rngFindResult.Row, 7).Value
wsUpdate.Cells(lUpdateRow, 8).Value =
wsMaster.Cells(rngFindResult.Row, 8).Value
End If
'move to next row on Update sheet:
lUpdateRow = lUpdateRow + 1
Application.CutCopyMode = False
Loop
End Sub

Macro to insert data from another sheet stops working
 

It's ok, I figured it out - I set "lastrow" to "ActiveSheet" when it
should have been "wsMaster".

Thanks anyways,

Nats


Nats wrote:
Hello,

I want to thank everyone for their help on this in advance!

I am currently working on a budget program in which I have a module
called InsertAdjustments. I call the module to insert adjustments from
a worksheet named Manual Adjustments which populates each sheet.
Unfortunately the code works for some sheets, but not others. It even
stops half way through one of the sheets. I have been looking on this
site to find some answers but haven't found anything yet.

Please help! My code was based off of a posting by "Dave". Here's
the code I'm currently using:

Sub InsertAdjustments()

Const cMasterKeyColumn = 3 'column number used for comparison
Const cUpdateKeyColumn = 3 'column number used for comparison

Dim wsMaster As Worksheet 'destination worksheet
Dim wsUpdate As Worksheet 'local worksheet
Dim rngMasterSearchRange As Range 'rng in master to search
Dim rngFindResult As Range 'rng in local to find
Dim lMasterLastRow As Long 'last row when appending in master
Dim lastrow As Long
Dim lUpdateRow As Long 'row to update copied from local sheet

Set wsMaster = ThisWorkbook.Worksheets("ManualAdjustments")
Set wsUpdate = ThisWorkbook.ActiveSheet
lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp)(2).Row
Set rngMasterSearchRange = wsMaster.Range("C2:C" & lastrow)
lUpdateRow = 8

Do While wsUpdate.Cells(lUpdateRow, cUpdateKeyColumn).Value < ""
Set rngFindResult = rngMasterSearchRange.Find( _
What:=wsUpdate.Cells(lUpdateRow, cUpdateKeyColumn).Value, _
LookIn:=xlValues, _
MatchCase:=False)

If rngFindResult Is Nothing Then
Else 'value found, so update values
wsUpdate.Cells(lUpdateRow, 6).Value =
wsMaster.Cells(rngFindResult.Row, 6).Value
wsUpdate.Cells(lUpdateRow, 7).Value =
wsMaster.Cells(rngFindResult.Row, 7).Value
wsUpdate.Cells(lUpdateRow, 8).Value =
wsMaster.Cells(rngFindResult.Row, 8).Value
End If
'move to next row on Update sheet:
lUpdateRow = lUpdateRow + 1
Application.CutCopyMode = False
Loop
End Sub