-1

In Sheet1 I have columns A and B. Those are empty. In Sheet2 I have columns A,B,C as well. In Sheet2 A and C columns are ppoulated, while B column is empty, and users need to populate this manually.

Once I populate the B column in Sheet2 for any of the existing rows, I should copy that row with A and B values and paste it to the Sheet1, by creating a new row in Sheet1 for A and B columns with same values ( A and B columns) from the Sheet2. That Sheet1 has some different calculation that should be kept in separate sheet, so I must make this kind of automation for creation of new row.

Is this feasible somehow?

vel
  • 1,000
  • 1
  • 13
  • 35
  • It is feasible with VBA. You will need to use the `worksheet_change()` function so that your code fires every time a change occurs on Sheet2. Inside that function is where you will write the logic to grab the values from columns A and B, determine the first unoccupied row in Sheet1, and then drop your values in. – JNevill Apr 19 '23 at 20:28
  • @JNevill thank you for your involvement. Could you please give me the example of the given function how it should be defined since I am not so skilled with it? if possible of course... thanks – vel Apr 19 '23 at 20:41
  • On Excel 365, it is possible without macro. – Atmo Apr 20 '23 at 13:57
  • @Atmo can you state how? – vel Apr 21 '23 at 06:36

2 Answers2

2

This will look something like:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Something changed, was it in column B?
    If Not Intersect(Target, Sheet2.Range("B:B")) Is Nothing Then
        'The change was in column B.
        'Lets figure out the last unused row in Sheet1
        lastRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row + 1
        
        'Now we will grab our values from A and B and stick them in Sheet1
        Sheet1.Range("A" & lastRow).Value = Target.Offset(, -1).Value 'col A for the row that changed
        Sheet1.Range("B" & lastRow).Value = Target 'col B for the row that changed
    End If
End Sub

That goes in to Sheet2's code page.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • I believe he wants the code to run if the changed cell is in Sheet2 Column B but your code is checking if the changed cell is in Column C – kevin Apr 19 '23 at 23:50
  • thank you kevin and JNevill. Yes, action should be triggered once the Column B in Sheet2 is populated. Can you please make correction for that purpose? – vel Apr 20 '23 at 09:17
  • can you also tell me where exactly I should add this function? and what did you mean by "worksheet_change()" function? does this need to be defined or not? – vel Apr 20 '23 at 09:26
  • Go into your excel workbook's visual basic editor. Find Sheet2 in the Left hand pane that is showing your workbook. Double click it to open it's code page. Copy and paste this code in and then save your workbook as a "Macro Enabled Workbook". – JNevill Apr 20 '23 at 13:44
  • What I mean by worksheet_change() function is that when we write code in VBA we have to put it inside of a subroutine or function. We can name these whatever we like, BUT there are a handful of special ones that trigger on certain events in the workbook. `Worksheet_Change()` is one of them that will trigger whenever anything changes in a worksheet. So we are using that function (actually a subroutine) to launch code that determines what changes and if it's something we are interested in, performing your copy/paste to Sheet1. – JNevill Apr 20 '23 at 13:46
  • @kevin I have updated the code to reflect that requirement. Thanks for the catch! – JNevill Apr 20 '23 at 13:47
0

In Excel 365 or Excel 2021, a FILTER formula should do the trick (see here for help).

=FILTER(Sheet2!A:B, NOT(ISBLANK(B:B)))

This creates a 2-column-wide spilled range wherever you type this formula.
Then, to build other formulas from the spill range, you can refer to the method/formulas I had typed in that other answer.

Atmo
  • 2,281
  • 1
  • 2
  • 21
  • where I should enter this formula? on one colum or in both? I don't understand... I need to copy values in both columns A and B but I am not able to enter this formula in a single columns since it will return an error – vel Apr 27 '23 at 23:51
  • my request is to populate this automatically, this should add rows automatically... as I see on the link you gave this is not the case with the FILTER function – vel Apr 27 '23 at 23:56
  • You have not checked the links I included in my answer, have you? The very clear video at the beginning of the Microsoft help page already answers both your questions: it shows the formula is typed in a single cell (not an entire column, not 2 columns) and it very clearly shows the formula does populate a range under `I1:M1`. The only difference with the video is the filter criterion, so all non blanks are included rather than 1 specific value. – Atmo Apr 28 '23 at 04:35