Macro to insert data from another sheet stops working  
Author Message
pauls1888





PostPosted: Sun Sep 03 17:30:20 CDT 2006 Top

Excel Programming >> 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

Excel268  
 
 
Nats





PostPosted: Sun Sep 03 17:30:20 CDT 2006 Top

Excel Programming >> 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



> 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