Auto Populate

[haylau]

Hi

Hard to explain this one.

In sheet 1 i have a list of products in Column A

Product1
Product2
Product 3
....
Product 3000

Etc

On sheet 2 I need to reference those cells, and expand it
So on sheet 2 I will end up with

product1
Product1
product1
product2
product2
product2
product3
product3
product3

etc.

In fact this list on sheet 2 will be expanded 20 times (so product 1 listed 20 times, then product2 listed 20 times etc)

This list is ongoing so when a new product is added on sheet 1 , i need to either automatically (or manually) update sheet 2)

My first thought was simply to replicate
So on sheet 2 have
=cell1
=cell1
=cell1
=cells2
=cells2
=cell2
etc, then replicate the above down, the next one woudld be=cell7 instead of =cell3

I hope that makes sense?

I have read a little about Rows() and Ofset(), but I just cant figure it out

Any ideas?

 

[Akuini]

In fact this list on sheet 2 will be expanded 20 times (so product 1 listed 20 times, then product2 listed 20 times etc)

This list is ongoing so when a new product is added on sheet 1 , i need to either automatically (or manually) update sheet 2)

Any ideas?

If you're ok to use VBA, I can write the code to do that. But I need to know how to determine which ones are the new items. How about this:
The code will copy the item (in sheet1 col A) starting from where you put the cursor and go down till to the last row.
So let's say you add new items in A100:A110, then you need to put the cursor at A100 then run the macro. The macro will copy the item in A100:A110 (expanded 20 times) to the other sheet.
Does this scenario suit you?

 

[haylau]

Hi

I think that could work

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab Where your original data will be entered
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter data in column A of original sheet 20 of these values will be entered in column A of Sheet(2)

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/3/2018  6:21:14 PM  EST
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Copy Sheets(2).Cells(Lastrow, 1).Resize(20)
Target.Offset(1).Select
End If
End Sub

 

[Akuini]

Hi

I think that could work

Ok, try this:
At first round, you already have some data, put the cursor at first data row, maybe A2? then run the macro.
Next time you add new items, put the cursor in the first new item, then run the macro.

Sub a1079619a()
'https://www.mrexcel.com/forum/excel-questions/1079619-auto-populate.html
Dim a As Long, b As Long, j As Long, k As Long, i As Long
Dim va As Variant, vb As Variant, msg As String

a = ActiveCell.Row
b = Range("A" & Rows.count).End(xlUp).Row
msg = "You're going to copy starting from cell A" & a & " ('" & ActiveCell & "')"
        If MsgBox(msg, vbOKCancel) = vbCancel Then Exit Sub

va = Range(Cells(a, "A"), Cells(b, "A"))
ReDim vb(1 To UBound(va, 1) * 20, 1 To 1)

For i = 1 To UBound(va, 1)
    For j = 1 To 20
        k = k + 1:  vb(k, 1) = va(i, 1)
    Next
Next

Sheets("Sheet2").Activate
b = Range("A" & Rows.count).End(xlUp).Row + 1
Cells(b, "A").Resize(UBound(vb, 1), 1) = vb

End Sub

 

[haylau]

Ok, try this:
At first round, you already have some data, put the cursor at first data row, maybe A2? then run the macro.
Next time you add new items, put the cursor in the first new item, then run the macro.

Sub a1079619a()
'https://www.mrexcel.com/forum/excel-questions/1079619-auto-populate.html
Dim a As Long, b As Long, j As Long, k As Long, i As Long
Dim va As Variant, vb As Variant, msg As String

a = ActiveCell.Row
b = Range("A" & Rows.count).End(xlUp).Row
msg = "You're going to copy starting from cell A" & a & " ('" & ActiveCell & "')"
        If MsgBox(msg, vbOKCancel) = vbCancel Then Exit Sub

va = Range(Cells(a, "A"), Cells(b, "A"))
ReDim vb(1 To UBound(va, 1) * 20, 1 To 1)

For i = 1 To UBound(va, 1)
    For j = 1 To 20
        k = k + 1:  vb(k, 1) = va(i, 1)
    Next
Next

Sheets("Sheet2").Activate
b = Range("A" & Rows.count).End(xlUp).Row + 1
Cells(b, "A").Resize(UBound(vb, 1), 1) = vb

End Sub

Thank, it does not quite work. But close. First time around it copied all data from sheet 1 to sheet 2, but offset by 1 row for some reason (data was in A1, paste started at A2. First time round I can probably work with that

Second time around, if I add one more cell data I get a type mismatch error, if I enter more than one cell of data it switches to sheet 2, BUT pastes the data backinto sheet 1

 

[haylau]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab Where your original data will be entered
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter data in column A of original sheet 20 of these values will be entered in column A of Sheet(2)

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/3/2018  6:21:14 PM  EST
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Copy Sheets(2).Cells(Lastrow, 1).Resize(20)
Target.Offset(1).Select
End If
End Sub

Could not get this to work at all . The code did not appear in the run macro box

 

[Akuini]

Thank, it does not quite work. But close. First time around it copied all data from sheet 1 to sheet 2, but offset by 1 row for some reason (data was in A1, paste started at A2. First time round I can probably work with that

I thought you have header in sheets2, I can modify the code to adjust that.

Second time around, if I add one more cell data I get a type mismatch error, if I enter more than one cell of data it switches to sheet 2, BUT pastes the data backinto sheet 1

I don't understand, I tried my code with no such problems.
Let's be clear:
1. Sheet1 must be the active sheet when you run the macro. You might want to put a button in sheet1 to run the code.
2. Sheet2 name is "Sheet2"
3. Say you add new items in A10:A15, after you finish adding the new data you must put the cursor back to A10 then run the macro.

 

[My Aswer Is This]

That's because you did not install it the way I described. Did you read my instructions?
This scripts runs automatically when you enter a value in column A

Could not get this to work at all . The code did not appear in the run macro box

 

[haylau]

Sorry, misunderstood. Yes that now works