Problems in Visual Basic Editior, In search of a command

[Matt G]

Overview of what I have done:

Using VB Editor I wrote instructions for copying data entries from m
primary worksheet to move my second sheet in my desired format. i.e
it is not an exact duplicate of my first worksheet.

* Basically 1 line of data being entered in my first sheet is copie
into four lines on my second sheet.


The Problem:

I am trying to write a command that will recognize the instructions
wrote as variable so I can make this format repeat as I continue t
make entries in my first worksheet over time.

I know that the command will have to recognize that the instructions
wrote will act in increments of four and I have tinkered with command
but have been unsuccessful. Any suggestions or tips would be ver
gratefully appreciated.

Below is a sample of the commands I wrote. The data from Sheet 1 is
single row of inputs that copy into four lines on the second sheet as
want them.

Sub Duplicate1()

Range("L2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("L4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("L5").Select

Range("M2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("M4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("M5").Select


End

For Example: On Sheet 1:

L = Quantity, and will copy to L on Sheet 2 but into four rows
M = Price, and will copy to M on Sheet 2 but into four rows


Matt S

 

[Tom Ogilvy]

do you want to accumulate values or will your enteries always be in L2:L5
and M2:M5

If the latter and entries always made in L2 and M2, once the formulas are
entered as you code does, the second sheet would always display 4 copies of
the current values.

Assume you are not always entering your values in sheet Entry in L2 and M2.
If not will you want both the entry of data and duplication of 4 instances
of each value to accumulate as well (entries progress down column L and M in
Entry and 4 copies progress down the second sheet).

Clarify and perhaps you can get answer that is consistent with your need.

 

[Matt G]

Hi Tom, Thanks for your response. You were able to articulate what
was trying to in my original post.

My desired reult is to have my entries from Sheet 1 progress dow
columns L and M duplicating in 4 instances

I have been trying variations of a vlookup to make this work a fe
minutes ago and still have had no success. I will keep trying.

Thanks again,
Mat

 

[Tom Ogilvy]

You don't totally clarify, but I will make an assumption. I will assume you
make entries in L2 and M2 and when you change the value in M2, you want the
values in L2 and M2 recorded as constants (4 times) in sheet2

right click on sheet1 and paste in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

If Target.Count > 1 Then Exit Sub
If Target.Address = "$M$2" Then
With Worksheets("Sheet2")
If IsEmpty(.Range("L2")) Then
Set rng = .Range("L2")
Else
Set rng = .Cells(Rows.Count, "L").End(xlUp)(2)
End If
End With
rng.Resize(4, 1).Value = Me.Range("L2")
rng.Offset(0, 1).Resize(4, 1) = Target
End If

End Sub


Always enter in L2 first, then M2