CODEWITHSUNDEEP
vbaexcel
csveinsson
csveinsson

Reputation: 3

Update Data in Other Excel Sheets of a Workbook

This is a direct reference to a previously asked and answered question located here: Automatically Update Data in Other Excel Sheets of a Workbook

That script works fine, but for some reason, it does not pull the entirety of the lists. The following is my code that I edited to work for my solution. I am not using this first block of code:
_

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Call UpdateFromMaster
End Sub

This is what I am using in a standard module:

_

Sub UpdateFromMaster()

Call ResetDestinationSheets


Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("Q" & i).Value = "Todd" Then Rows(i).Copy Destination:=Sheets("Todd").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("Q" & i).Value = "Bob" Then Rows(i).Copy Destination:=Sheets("Bob").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("Q" & i).Value = "Trevor" Then Rows(i).Copy Destination:=Sheets("Trevor").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i

End Sub

_

Sub ResetDestinationSheets()


Call ResetThisSheet("Todd")
Call ResetThisSheet("Bob") 
Call ResetThisSheet("Trevor")
End Sub

_

Sub ResetThisSheet(ByRef SheetToClear As String)
Sheets(SheetToClear).Range("A2:S" & Rows.Count).Clear
End Sub

_

Now, my master list after running the UpdateFromMaster Macro, the Macro seems to work fine. Until I check the number of leads in each worksheet (Todd, Bob, and Trevor). There should be 39 for Bob, 243 for Todd, and 62 for Trevor. When the Macro is run, it only sends 105 to Todd's worksheet, 39 to Bob's (which is correct), and 51 to Trevor's. I do have a heading row on all worksheets including the Master.

As I troubleshot this, I cleared all formatting on all worksheets and double checked that everything is spelled right in the "Q" column.

Any ideas on what I am doing wrong?

Upvotes: 0

Views: 934

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

It looks like your input will stop on the first row that is blank in column A. This line here:

LR = Range("A" & Rows.Count).End(xlUp).Row

First gets a range of all of the "potential" rows in column A (Range("A" & Rows.Count)). Depending on your version of Excel and whether you've saved it recently, this "potential" row count could include thousands (or even millions) of rows that are valid, but are not actually being used.

To alleviate this, the original coder added .End(xlUp).Row which tells it to vertically go to the last cell in the column (it's the same thing that you would get if you pressed "control-Down" on cell A1). The limitation of this approach is that the "last used cell" is defined locally, not globally, which means it stops at the first balnk cell in it's way.

Upvotes: 1

Related Questions