Mirror cells from one sheet to another within the same workbook with special conditions

Question

What are you trying to accomplish?
I'm trying to mirror the contents of a row of respective cells from one sheet to another. However, the caveat here are: I'm restricted to MS Excel not Access - I can use modules, the initials in column B are to be used as the condition to populate the corresponding sheets. Like: Primary key in MS Access and finally the sheets should be populated in real time; nothing should have to be run. Said spreadsheet attached here.
What have you tried so far?
a. Well, I tried the =Value(x) and =Grand Budget!A(x) formulas. I setup the corresponding initials worksheets with the respective initials in the main sheet so for example: on sheet "Grand Budget" all NA initials and data in the corresponding row i.e. Description, Vendor and Amount will be mirrored on worksheet "NA"
It works great until the third caveat is unfulfilled: it doesn't work in real time because I cannot predict what the next initial will be so I can't setup the next cell with a formula.
b. I also tried filtering the "Grand Budget" sheet by initials in column B, selecting the needed row data, named it and imported the named table to the corresponding initial worksheet. Source.
What do you have in mind - conceptually?
I'm thinking, it's time for modules or if someone knows a hidden excel formula, lay it on me.
all I have right is an if statement in c++ in my mind: if(current_cell == desired_initial) {populate()};

I'm willing to aid in interpreting anything that may have been transposed incorrectly.

What exactly do you mean by "in real time"? When column B is filled? Or whenever column C, D or E changes? — Peter Pesch, Dec 06 '17 at 21:35
just the initial of the respective worksheet. doesn't matter if its repeated or redundant. I'm to have four columns so. — , Dec 06 '17 at 21:35
What do you want to happen if the user goes back to a previous row and changes the value in column B? — Peter Pesch, Dec 06 '17 at 21:36
Do the rows in the Grand Budget have some information which can be used to uniquely identify them? — Peter Pesch, Dec 06 '17 at 21:40
i was instructed to do both: to cover for human error, hence instead of NA incurring charges, LMK gets charged, a quick edit in column B should revert the error and charge the appropriate initial; and as column B and its respective data is filled, the corresponding sheet is populated when the user strikes enter for the next row of record so: IKD,Books,Amazon,50.00 [enter] [IKD sheet is populated/appended] — , Dec 06 '17 at 21:40
the unique identifiers are the initials: NA, LMK, KL, JC, IKD and FO. Those are kinda like the primary key-ish. — , Dec 06 '17 at 21:43

score 1 · Answer 1 · answered Dec 07 '17 at 21:22

1

@peter update on progress, I require assistance on how to populate the respective sheets. thank you!

Option Explicit

' Create a new sheet for new initials.
Sub CreateNewSheet(Initials As String)
    'Checks whether a sheet with the name "Initials" exists
    'Creates a new sheet if it doesn't exist yet.
    If SheetExists("Initials") Then
        MsgBox "Yay! the sheet exists"
    Else
        MsgBox "Boo! the sheet doesn't exist; I shall create a new one!"
         'Creates a new sheet if it doesn't exist yet.
         CreateSheet (Initials)
    End If
End Sub

Function SheetExists(Initials As String)
    On Error GoTo no:
    WorksheetName = Worksheets(Initials).Name
    SheetExists = True
    Exit Function
no:
    SheetExists = False
End Function

Private Sub CreateSheet(Initials)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = Initials
End Sub

Private Sub sbClearEntireSheetOnlyData()
    Sheets("Initials").Cells.ClearContents
End Sub

Private Sub MatchCase(Initials)
    Dim x As Integer
    Application.ScreenUpdating = False
    ' Set numrows = number of rows of data.
    NumRows = Range("A3", Range("A3").End(xlDown)).Rows.Count
    ' Select cell a3.
    Range("A3").Select
    ' Establish "For" loop to loop "numrows" number of times.
    For x = 1 To NumRows
       ' Insert your code here.
       ' Selects cell down 1 row from active cell.
       ActiveCell.Offset(1, 0).Select
    Next
    Application.ScreenUpdating = True
End Sub

answered Dec 07 '17 at 21:22

1

Currently, your code isn't working because of the `Option Explicit`. You should either declare all your variables (which helps in improving your program), or leave out the `Option Explicit` statement. See http://www.excel-easy.com/vba/examples/option-explicit.html – Peter Pesch Dec 08 '17 at 05:56
1

A tiny mistake in your `Sub CreateNewSheet`: `If SheetExists("Initials") Then` should be `If SheetExists(Initials) Then` – Peter Pesch Dec 08 '17 at 05:58
1

I think your 'CreateSheet' sub should add the header row after creating the new sheet. That mught save you a lot of time further on... You might use code like `ws.[B1].Value = "Description"` to fill your header row. – Peter Pesch Dec 08 '17 at 06:01
Your `Sub sbClearEntireSheetOnlyData` needs some work ... (But looking at your code so far, I'm sure you can fix that yourself today). – Peter Pesch Dec 08 '17 at 06:03
1

I do not understand the name of your `Private Sub MatchCase`. As you are changing the `.ScreenUpdating` setting here, I will assume it contains your main loop. You might consider giving this Sub a name which explains what the sub is going to do. – Peter Pesch Dec 08 '17 at 06:59
1

Your NumRows calculation is not working for your sheets. For the time being, you might use `wsBudget.UsedRange.rows.count` for your main sheet (assuming that you want to process all data, neglecting the month), or `wsInitial.[A10000].End(xlUp).row` for the other sheets. – Peter Pesch Dec 08 '17 at 06:59
1

In your main Sub, you will need 2 different parts: (1) Initialization and (2) Processing the data. – Peter Pesch Dec 08 '17 at 06:59
1

During your Initialization, you'll have to loop through all (relevant) rows in your `Grand Budget`, and make sure that every `Initial` has got a sheet with a header, but without data. I'm sure that you can manage this part yourself today. – Peter Pesch Dec 08 '17 at 07:00
1

During your Processing, you'll have to loop through all (relevant) rows in your `Grand Budget` again. For each row, you'll have to find out which `Initial` this row is about (you can manage that yourself). Then you should find the correct sheet and the last row which has already been filled (you can manage that yourself). Finally, you'll have to copy the 4 cells which contain the data. – Peter Pesch Dec 08 '17 at 07:00
1

With respect to copying the 4 cells: A statement which works might be `wsBudget.Rows(currentBudgetRow).Range("B1:E1").Copy (wsInitial.Rows(numRowsInitial + 1).Range("A1"))` (Can't post a more readable form in the comments, sorry.) – Peter Pesch Dec 08 '17 at 07:00
1

About the left hand side ot my Ugly Statement: `wsBudget.Rows(currentBudgetRow)` is the Range containing the entire row we are currently processing. `.Range("B1:E1")` means that we are only looking at the 2nd to 5th columns of that row. Your program belongs much more readable (and maintainable!) if you put this range into some variable first. – Peter Pesch Dec 08 '17 at 07:13
1

About the right hand side of my Ugly Statement: `wsInitial.Rows(numRowsInitial + 1)` is the first empty row on the sheet for the `Initial`. `.Range("A1")` means (that the destination of the copy action will be) the range starting at the upper left hand corner of this range. You might consider using `.Range("A1:D1")` instead, to make it clear that only fr cells will be written. Again, please do use variables to make it more readable. – Peter Pesch Dec 08 '17 at 07:20
1

I hope this gives you enough guidance to complete the actual programming part of the problem. – Peter Pesch Dec 08 '17 at 07:24
thank you! i wish i could mark your comments as answers! the completed code will be up by tonight for peer review – Dec 08 '17 at 21:10

Peter Pesch · Answer 2 · 2017-12-07T06:52:06.913

Apparently there is no way to uniquely identify the rows on the Grand Budget sheet, we only know the sheet on which the row should go.

So if you really need to do this Real Time, I'm afraid you'll have to repopulate the entire user-sheet whenever there is a change anywhere in columns C-E of the budget sheet. And if there's a change in column B, you'll have to completely repopulate 2 user-sheets... That tends to be a very error-prone process, so it's probably best to completely repopulate all user-sheets ...

My advice would be to ask your client to change the requirements... For instance, ask them if it's okay to have some Recalculate button to start your process.

If your client really doesn't want to use a button, you could ask if it's okay if your macro (which repopulates all user-sheets) starts automatically whenever you open the workbook and whenever you leave the Grand Budget sheet (which in effect also is the only input sheet).

By the way: What do you want to happen if they put nonexistent initials in column B?

Update: "I just spoke with said client, and i quote: "do what you must!" I like the recalculate button idea, please do pitch it to me!" – Nana Amponsah

If you don't have much experience with vba, it might be best to start with the second part: Create a new sheet for new initials.

Option Explicit

Sub CreateNewSheet(Initials As String)
    'Checks whether a sheet with the name "Initials" exists
    'Creates a new sheet if it doesn't exist yet.

    '*** Your code goes here ***
End Sub

Please Note: Before you start, you should make a backup of the original workbook.

The first part of this page tells you how to make a module for your program.

And you might have a look at:

I just spoke with said client, and i quote: "do what you must!" I like the recalculate button idea, please do pitch it to me! — , Dec 06 '17 at 22:03
OK: For column B, you should probably set up a validation which prevents input of nonexistent initials. Preferably by using a table on some settings sheet. — Peter Pesch, Dec 06 '17 at 22:11
First part of your macro should be to clear all user-sheets. Second part would be to add new sheets for all new initials ... — Peter Pesch, Dec 06 '17 at 22:14
Third part of your macro should loop through all rows of your budget sheet, and copy each row to the correct sheet. — Peter Pesch, Dec 06 '17 at 22:15
Sorry, I don't have a computer nearby. (It's almost midnight over here). I'll have a look in the morning. — Peter Pesch, Dec 06 '17 at 22:24
understandable, we have until this Saturday. I'll do some reading on macros and modules etc; thank you for your help. I'll wait your reply and expertise, until then, I'll mark this question unanswered in case others may be readily available. again, thank you — , Dec 06 '17 at 22:32

Mirror cells from one sheet to another within the same workbook with special conditions

2 Answers2