how to add the above row x times in excel based on the number y which can change

[BlackHairSasha]

I am trying to make an excel file for my parents so that they have it easier than writing all the info in a book X amount of times.

i have A; B; C; D; E; F; G; H; I; J; and L columns and want to automate and duplicate the data in A to G in rows below xn-1 times when

there is a number on Hx cell x amount of times,

where x can be from 1 to 50.




before typing 10




example data after inputting 10 or 5 respectively

thus will be inputting all the details in the rows 17 and 27

I tried VBA but I need to go and click macro every time. is there it can be used via formula, please?

 

[Snakehips]

@BlackHairSasha Welcome

If you are not averse to using vba then it should be far more efficient than messing with formulas?

Maybe try the below pasted into the sheet's code module.
That way, it will trigger automatically and not require a button.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim MyRng As Range
 Dim os As Integer
 
  If Not Target.Column = 8 Then Exit Sub
  If Not Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then Exit Sub
  If Target = vbNullString Then Exit Sub
  If Not IsNumeric(Target) Then Exit Sub
 
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Set MyRng = Range("A" & Target.Row & ":G" & Target.Row)
 
  For os = 1 To Target - 1
    MyRng.Offset(os, 0).Value = MyRng.Value
  Next os
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

Hope that helps.

 

[BlackHairSasha]

@BlackHairSasha Welcome

If you are not averse to using vba then it should be far more efficient than messing with formulas?

Maybe try the below pasted into the sheet's code module.
That way, it will trigger automatically and not require a button.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim MyRng As Range
 Dim os As Integer
 
  If Not Target.Column = 8 Then Exit Sub
  If Not Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then Exit Sub
  If Target = vbNullString Then Exit Sub
  If Not IsNumeric(Target) Then Exit Sub
 
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Set MyRng = Range("A" & Target.Row & ":G" & Target.Row)
 
  For os = 1 To Target - 1
    MyRng.Offset(os, 0).Value = MyRng.Value
  Next os
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

Hope that helps.

hi,

thank you for ur time and effort but i already i have the VBA code which can be found bellow

Sub Autorows()
Dim wsc As Worksheet 'worksheet copy
Dim wsd As Worksheet 'worksheet destination

Dim lrow As Long 'last row of worksheet copy
Dim crow As Long 'copy row
Dim drow As Long 'destination row

Dim multiplier As Integer
Dim i As Integer 'counting variable for the multiplier

Set wsc = Sheets("Sauda Entry")
Set wsd = Sheets("Sauda Entry with Design")

lrow = wsc.Range("h" & wsc.Rows.Count).End(xlUp).Row
drow = 2

With wsc

For crow = 2 To lrow 'starts at 2 because of the header row

multiplier = .Cells(crow, 8).Value 'copies the value in column h

For i = 1 To multiplier

wsd.Cells(drow, 1).Value = .Cells(crow, 1).Value
wsd.Cells(drow, 2).Value = .Cells(crow, 2).Value
wsd.Cells(drow, 3).Value = .Cells(crow, 3).Value
wsd.Cells(drow, 4).Value = .Cells(crow, 4).Value
wsd.Cells(drow, 5).Value = .Cells(crow, 5).Value
wsd.Cells(drow, 6).Value = .Cells(crow, 6).Value
wsd.Cells(drow, 7).Value = .Cells(crow, 7).Value
wsd.Cells(drow, 8).Value = .Cells(crow, 8).Value


drow = drow + 1 'increasing the row in worksheet destination

Next i

Next crow

End With

End Sub
*******************************

it gives me the output in a new sheet which I m ok with.

now I have more questions as my parents say that they can have different quantity sent on different dates for the same order code
for example if "Quantity Remaining" is "0" its well and fine but it its above that they need to send remaining at a later date upto a maximum quantity (factor is the volume / quality) which varies depending on the type quality
how do i incorporate that i on the excel file that if there is quality remaining it provides them with a colum for a future date...sorry i dont eve have a logic to this problem

autosauda book1
	A	B	C	D	E	F			I	J	K	L	M
1	Order Number	Date	Buyer Name	Quality	Weaver Name	Rate			Quantity per Design	Design Code/Number	Quantity Sent	Quantity Sent - Date	Quantity Remaining
2	7865	19/09/2022	abc	A123	xyz	35			50,0	a123	50	19/09/2022	0
3	7865	19/09/2022	abc	A123	xyz	35			50,0	b123	25	19/09/2022	25
4	7866	20/09/2022	abv1	e123	adsf	45			6,3	a124			6
5	7866	20/09/2022	abv1	e123	adsf	45			6,3	b124			6
6	7866	20/09/2022	abv1	e123	adsf	45			6,3	a125			6
7	7866	20/09/2022	abv1	e123	adsf	45			6,3	b125			6
8	7866	20/09/2022	abv1	e123	adsf	45			6,3	a126			6
9	7866	20/09/2022	abv1	e123	adsf	45			6,3	b126			6
10	7866	20/09/2022	abv1	e123	adsf	45			6,3	a127			6
11	7866	20/09/2022	abv1	e123	adsf	45			6,3	b127			6
12	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	a128			9
13	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	b128			9
14	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	a129			9
15	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	b129			9
16	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	a130			9
17	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	b130			9
18	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	a131			9
19	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	b131			9
20	7867	21/09/2022	weF	WEFFR	CQ	50			8,7	a132			9
21	7868	22/09/2022	RZ	RTU	dffg	4			114,8	b132			115
22	7868	22/09/2022	RZ	RTU	dffg	4			114,8	a133			115
23	7868	22/09/2022	RZ	RTU	dffg	4			114,8	b133			115
24	7868	22/09/2022	RZ	RTU	dffg	4			114,8	a134			115
25	7868	22/09/2022	RZ	RTU	dffg	4			114,8	b134			115
26	7869	23/09/2022	sasha	erg	qq	7			50,0	a135			50
27	7869	23/09/2022	sasha	erg	qq	7			50,0	b135			50
28	7869	23/09/2022	sasha	erg	qq	7			50,0	a136			50
29	7869	23/09/2022	sasha	erg	qq	7			50,0	b136			50
Sauda Entry with Design

Cell Formulas
Range	Formula
I2:I29	I2	=G2/H2
M2:M29	M2	=I2-K2

 

[Snakehips]

If you are happy with your vba code then you can still call it using the Worksheet Change event of the Sauna Entry sheet, similar to below,
Or you attach your macro to a button that can reside at the top of the sheet?
Do you realise that your code rewrites the whole listing in the Sauna Entry With Design sheet, top to bottom, each time it runs?

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim MyRng As Range
 Dim os As Integer
 
  If Not Target.Column = 8 Then Exit Sub
  If Not Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then Exit Sub
  If Target = vbNullString Then Exit Sub
  If Not IsNumeric(Target) Then Exit Sub
 
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Call Autorows
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

As for your second question, I'm sorry but I do not understand the scenario you are describing.