|
|
Macro to insert data from another sheet stops working |
|
Author |
Message |
pauls1888
|
Posted: 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
|
Posted: 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
|
|
|
|
|
|
|