Autofill Question


Help, I am having trouble getting the proper referenced cells in my original sheet (Sheet1). I have sheet one with thousands of accounts. Those are filtered and copied to new sheets in the workbook. I  then want to filter the Sheet1 and have the last column in the sheet  be updated with the individual page results. Problem is when I run the macros it adds the proper page name but wrong rows. The rows being autofilled coorespond with the original number not the sheets numbers. I kinda confused myself with that but Here it is:

ActiveSheet.Range("$A$1:$X$5000").AutoFilter Field:=1, Criteria1:= _
    With Worksheets("Sheet1").AutoFilter.Range
       Range("W" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With
    ActiveCell.FormulaR1C1 = "='Sheet2'!RC23"
    With Worksheets("Sheet1").AutoFilter.Range
       Range("W" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    LR = ActiveSheet.UsedRange.Rows.Count
    Selection.AutoFill Destination:=Range("W2:W" & LR).SpecialCells(xlCellTypeVisible)
    End With



Please try this code. Install it in a standard code module in your workbook (by default "Module1").

Option Explicit
Private Function ResultColumn(Master As Worksheet, _
                              RowColumn As Long) As Long
    ' 28 Sep 2017
    ' it is presumed that the Results column is the
    ' last column on the right of the "Master" sheet,
    ' and has a column caption in Row 1
    ' If you choose another column, this code must be replaced
    ' as well as the offset from it by which the RowColumn is identified.
    Dim Fun As Long
    With Master
        Fun = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    ResultColumn = Fun
    RowColumn = Fun + 3         ' 3 columns right of the Results
End Function
Sub CreateAgentCopies()
    ' 28 Sep 2017
    Dim Ws As Worksheet
    Dim Rng As Range
    Dim Cell As Range, Tmp As String
    Dim Spike As String
    Dim Sp() As String, i As Long
    Dim Rl As Long
    Dim RowColumn As Long
    ' RowColumn will be used to match row numbers in the Master
    ' and the Agents' sheets
    Application.EnableEvents = False
    ' give a name to this sheet. If you change the name
    ' use Edit/Replace to replace all instances in this project
    With Worksheets("Master")
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' Column A has the agents' names, ignore row 1 (= caption)
        Set Rng = .Range(.Cells(2, "A"), .Cells(Rl, "A"))
        ResultColumn Rng.Parent, RowColumn              ' sets RowColumn
        For Each Cell In Rng
            Tmp = Cell.Value
            If InStr(1, Spike, Tmp, vbTextCompare) = 0 Then
                If Len(Spike) Then Tmp = "|" & Tmp
                Spike = Spike & Tmp
            End If
            Cells(Cell.Row, RowColumn).Value = Cell.Row
        Next Cell
        ' extend the range to include the added column
        Set Rng = .Range(.Cells(1, "A"), .Cells(Rl, RowColumn))
        Sp = Split(Spike, "|")
        For i = 0 To UBound(Sp)
            On Error Resume Next
            Set Ws = Worksheets(Trim(Sp(i)))
            If Err Then
                Set Ws = Worksheets.Add(After:=Sheets(Worksheets.Count))
                Ws.Name = Trim(Sp(i))
            End If
            Rng.AutoFilter Field:=1, Criteria1:=Sp(i)
            With Ws.Cells(1, 1)
                .PasteSpecial Paste:=8              ' copy the column widths
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
            End With
            Ws.Columns(RowColumn).Hidden = True
        Next i
        .AutoFilterMode = False
        Application.CutCopyMode = False
    End With
    Application.EnableEvents = True
End Sub
Sub UpdateMaster()
    ' 28 Sep 2017
    Dim Master As Worksheet
    Dim Rl As Long
    Dim RsltColumn As Long, RowColumn As Long
    Dim R As Long
    Set Master = Worksheets("Master")
    RsltColumn = ResultColumn(Master, RowColumn)
    Application.EnableEvents = False
    With ActiveSheet
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
        For R = 2 To Rl
            Master.Cells(.Cells(R, RowColumn).Value, RsltColumn).Value = .Cells(R, RsltColumn).Value
        Next R
    End With
    Application.EnableEvents = True
End Sub

Please name your master worksheet "Master" or change the name by which this worksheet is referred to in the code to whatever you name that worksheet. It should have a proper name.

Run the procedure "CreateAgentCopies" to create new worksheets for all agents. If the sheets already exist all data will be deleted from them, else they will be created.

In this process a column is added in which the program writes the row numbers. This extra column is copied to the agents sheets where it is hidden. On the Master it is deleted after copying.

Run the procedure "UpdateMaster" to copy results from the agent sheets to the Master. This procedure refers to the "ActiveSheet", meaning you must select the agents sheet you wish to update from, perhaps inspect it, and then run the code.

The function "ResultColumn" is for use of the other two procedures and intended to join the column management in one place. Please read my comments in this function. You can define the ResultColumn in some other way, like the "3rd column from the right". I think you can't define is by counting from the left because the number of your columns varies. But you must define the RowColumn relative to the ResultColumn.  Since it is never visible, you can place it very far from the others, if you like.



Thanks for the reply. I am relatively new to all of  this so I appreciate the help and hope I understand it. Now where you said: "The macro should first write the results in Sheet1 and then copy them to the new sheets." The new sheets are where the results are being recorded by individuals and what I need the first sheet to update to based on those results. When I did this with the specific numbers it filled the cells correctly. It was only when I started trying to find the top cell in the filtered data to write and fill the column that it did it wrong. Basically I have a bunch a data the needs to be verified on the first sheet. I filter by agent and copy that to the agents pages. They then verify the data and change the dropdown in the last column with their results. I want the column in the first sheet to update with those results. Is there a better way to do this? 
Wyldstylz56 Sep 26, '17 at 8:12 am
In that case you should treat Sheet2 as a data entry form. Data are transferred to Sheet1 either at the click of a button or when the user leaves Sheet2. The transfer shouldn't be made by means of worksheet formulas. To identify the rows to which data are to be written you should copy the row numbers to Sheet2. That is a cumbersome business. Would you mind sharing a copy of your Sheet2 to get an idea of what kind of data need to be captured? There ought to be a better way to create a data entry form than to filter half a database.
Variatus (rep: 823) Sep 26, '17 at 9:09 am
                  For this example, the is the main sheet. Thisinfo is filtered by Agent and that is then copied and pasted to their sheets Agent1,Agent2). They then update the results column on their page and it updates here. The main problem is, the number of columns changes as do the rows on any given day. The number of agents is almost the only constant.   Though that too will change occasionally. I need the results row here to update with thier info from thier pages. I thought my code had it looking for the first filtered row in the reults column and writing the formula for thier page reults coumns and copying it down to the last visible row in the filtered data. Am I going about this the wrong way?
Wyldstylz56 Sep 27, '17 at 1:39 pm
Sorry, I can't make out what that is. You can attach a workbook to your original question. Just make sure to remove all things personal.
Variatus (rep: 823) Sep 27, '17 at 11:20 pm
Added a workbook. Hope that helps clear it up.
Wyldstylz56 Sep 28, '17 at 8:01 am
That is very close and it could be user error but I am getting an Object defined error when running "UpdateMaster". I also noticed if I add a column to the master before running Createagentsheets it will still only grab up to the previous column.  Also when running in my actual workbook (columns up to V) It doesnt show row numbers hidden orotherwise. I'm hoping its user error. I have added another workbook with my resultsand the error. 
Wyldstylz56 Sep 28, '17 at 2:27 pm
When your code crashes please be sure to state the line of code on which the crash occurs and the precise error message. However, I think all the faults you report are related to your (not) handling of the columns issue I pointed out and which is the reason why I placed that otherwise unimportant function at the top of your code. That function needs to find the ResultColumn, then inserts the RowColumn x steps to the right of it, and that is the right-most column which gets included in the agents copies. I guess that you insert a column without caption. Perhaps you like to insert columns to the right of the ResultColumn. You need not change your ways, but if you want me to suggest code to you which works with the way you work you will have to tell me how to find the ResultColumn, regardless of how many columns you insert, and how many columns you want to insert to the right of it. Please bear in mind that you never showed me your worksheet. You keep me groping in the dark and then complain that I guide you the wrong way, lol:
Variatus (rep: 823) Sep 28, '17 at 9:19 pm
I'm defintely not complaining. I appreciate the help you are providing. I did update the test worksheet with my results  but I will always be adding the results column to the right of the last column on the sheet. So I did not mess with that portion of this when running the code you provided. The last page of the  sheet has the error and the line of code.  Thanks again. 
Wyldstylz56 Oct 3, '17 at 1:06 pm
According to my instructions, the Results column "must be the last column on the right of the "Master" sheet and must have a caption in row 1".
My design would have you add extra columns, as many as you wish, to the left of the Results column. You say that you always add extra columns on the right of the sheet, meaning to the right of the Results column. That is no problem at all, but the logic would require then for the Results column to be always in a fixed place. Which column is it? You can have it either way. It isn't a big effort. In fact, I designed the code to make that change easy.the last one on the right (or the second or third to last, if you wish) or it might be found by any other generic description. One description I used was "it has a caption in row 1". That isn't hewn in stone, either. Any way to help VBA find that column is acceptable.
Just in case: I propose that the Results column exists on the Master sheet and is copied form there to the Agents sheets. This is the way in which my code finds the column on the Agents sheets. If you have a different way or working, please describe your way and chances are that the code can be adapted.
Variatus (rep: 823) Oct 3, '17 at 9:57 pm
@Don I found a programming error here:
After scrolling to the last comment I made, I clicked "Edit" and the comment opened without the "Post" and "Cancel" buttons. In their place there is an "Add to the Discussion" button which opens a new discussion box, complete with its controls which, however, don't give control over the comment open for editing. The edit can't be completed. To get out I returned to the previous page which was the list of links to recent posts from where I returned to this post four times, trying to amend, in vain, with identical result each time.
Variatus (rep: 823) Oct 3, '17 at 10:13 pm
A little clarification. I only add a results column to the end of the master sheet once I get it. I do not add any other columns and I always add the results to the right of the last available column. Using the code you provided, right now it only copy and pastes the last row before I created the reults column. It also does not tranfer the row numbers to the child sheets. regardless of me creating the results column or not. What am I doing wrong? 
Wyldstylz56 Oct 5, '17 at 8:09 am
If you don't give a results column to the agents, where can they write their results? My code requires the results column to be the last column on the right of the master sheet and have a caption. I don't mean to impose this rule upon your processes. If you need to give the copies to the agents without a Results column I can change the way the code works.
Variatus (rep: 823) Oct 5, '17 at 9:28 am
I do give them to the agents. The code as is, only copy and pastes up to the last cloumn. When I add the reults column then run the macro, it copy and pastes everything up to right before the results column I added. It doesnt paste the results column to the agents pages or add the correlation to the rows. 
Wyldstylz56 Oct 5, '17 at 1:33 pm
The code will copy everything from the Master up to the last filled cell in row 1 and below. The last filled cell is supposed to be the caption of the results column. If this doesn't happen please post your workbook with Master and code. You can attach it to your original question. Make sure you "sanitize" it for public viewing.
Variatus (rep: 823) Oct 5, '17 at 9:45 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login