Macro that copie a row and paste 500 rows

[leobrice]

Hello friends of MrExcel good morning.
I wanted to ask for your help. Is it possible a macro that copies row 5 (A5.G5) and paste it after the last available row, taking as reference column G that has formula, about 500 rows.
NOTE: Row 5 will always be hidden
Thanks

CALCULADORA DE PRECIOS Y COSTOS DATCHEL PLUS4.xlsm
	A	B	C	D	E	F	G
1	Mi Empresa
2	COSTOS DE PRODUCTOS Y SERVICIOS NACIONALES
3	PRODUCTO / SERVICIO	NACIONAL	UNIDAD DE MEDIDA	CANTIDAD DE PRODUCTO	MONTO TOTAL DE LA COMPRA	COSTO PRODUCTO UNITARIO
4					$	$
5		NACIONAL				0,00	0
6	PRODUCTO 1	NACIONAL	Frasco	10,00	10.000,00	1.000,00	por Frasco
7	PRODUCTO 2	NACIONAL	Litro	2.000,00	250.000,00	125,00	por Litro
8	PRODUCTO 3	NACIONAL	Paquete	100,00	45.000,00	450,00	por Paquete
9	PRODUCTO 4	NACIONAL	Kilo	12,00	2.600,00	216,67	por Kilo
10	PRODUCTO 5	NACIONAL	Galón	100,00	325.000,00	3.250,00	por Galón
11	PRODUCTO 6	NACIONAL	Envase	1.500,00	360.000,00	240,00	por Envase
12	PRODUCTO 7	NACIONAL	Envase	20.000,00	650,00	0,03	por Envase
13	PRODUCTO 8	NACIONAL	Envase	200,00	10.000,00	50,00	por Envase
14	PRODUCTO 9	NACIONAL	Envase	150,00	140.000,00	933,33	por Envase
15	PRODUCTO 10	NACIONAL	Envase	200,00	250.000,00	1.250,00	por Envase
16	PRODUCTO 11	NACIONAL	Caja	1.500,00	350.000,00	233,33	por Caja
17		NACIONAL				0,00	0
18		NACIONAL				0,00	0
19		NACIONAL				0,00	0
20		NACIONAL				0,00	0
21		NACIONAL				0,00	0
COSTOS PRODUCTOS NACIONALES

Cell Formulas
Range	Formula
F5:F21	F5	=IFERROR(E5/D5,0)
G5:G21	G5	=C5
B5:B21	B5	=$B$3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:G10000	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E5:E21	Any value
A5:B10000	Custom	=CONTAR.SI($A$5:$A$10000;A5)<=1
C5:C10000	List	=UNIDADES

 

[leobrice]

The original file has 10,000 rows but is very heavy. What I want to do is to make a base file with 1,000 rows and then add rows (500 by 500) according to the user's need.

 

[Snakehips]

@leobrice Might you be as well to have this as a table so that it automatically expands as data rows are added?

 

[leobrice]

@leobrice Might you be as well to have this as a table so that it automatically expands as data rows are added?

@leobrice Might you be as well to have this as a table so that it automatically expands as data rows are added?

Thank you very much @Snakehips​

To be honest, I am old school, I have heard about the use of tables and their benefits but I have never applied them. I did some testing right now and it really is amazing. It does what I want without much effort,​

The question I have is the following. The workbook has 15 sheets, all with macros. Many macros have the range of 10,000 rows for execution.​

Does this change to tables affect the execution of the macros?​

I even think I can extend the range to 100,000 rows?​

Thanks​

Translated with DeepL.com (free version)

 

[Snakehips]

@leobrice I would imagine that using a table should not adversely affect the function of your macros. However the only way for you to be 100% sure might be for you to do some testing with extended data?
If you do not wish to proceed with using a table then maybe something like the below will enable you to extend the formulas as and when you see fit?

Sub AddRows()
Dim ws As Worksheet
Dim le As Long
Dim lf As Long
Dim rws As Long

Set ws = ActiveSheet
'last entry row determined by data in column C
le = ws.Range("C" & Rows.Count).End(xlUp).Row

'last formula row determined by data in column B
lf = ws.Range("B" & Rows.Count).End(xlUp).Row
Debug.Print le & "     " & lf
' if empty data rows greater than 10 (??)  then do nothing
If lf >= le + 10 Then Exit Sub  'edit to suit

'Otherwise Add 'rws' new Data rows
rws = 500  'edit to suit
ws.Range("A" & lf & ":G" & lf).AutoFill Destination:=ws.Range("A" & lf & ":G" & lf + rws)

Set ws = Nothing
End Sub

HTH

 

[Snakehips]

If you do use the above, delete the Debug.Print line !!!

 

[leobrice]

Si utiliza lo anterior, elimine la línea Debug.Print.

Gracias @Snakehips
Me gustan mucho ambas opciones, incluso me inclino por las tablas, pero tengo un problema. Todas las hojas del archivo están protegidas y al parecer el proceso de autocompletar filas de tablas no funciona en hojas protegidas. Por lo que la opción de macro me funcionaría manteniendo las hojas protegidas.
Lo que me gustaria es colocar despues de la ultima fila disponible una fila avisando el fin de filas disponibles y que tendra que sumar 500 filas, entonces al ejecutar la macro esa fila bajara 500 filas y volveria a ser la ultima.

Traducido con DeepL.com (versión gratuita)

Algo como esto

EXAMPLE (2).xlsm
	A	B	C	D	E	F	G	H	I
88	PRODUCTO 37	NACIONAL	Kilo	24,00	1.000,00	41,67	por Kilo
89	PRODUCTO 38	NACIONAL	Kilo	24,00	1.000,00	41,67	por Kilo
90	PRODUCTO 39	NACIONAL	Kilo	1,00	1.000,00	1.000,00	por Kilo
91	PRODUCTO 40	NACIONAL	Kilo	24,00	1.000,00	41,67	por Kilo
92		NACIONAL				0,00	0
93		NACIONAL				0,00	0
94		NACIONAL				0,00	0
95		NACIONAL				0,00	0
96		NACIONAL				0,00	0
97		NACIONAL				0,00	0
98		NACIONAL				0,00	0
99		NACIONAL				0,00	0
100		NACIONAL				0,00	0
101	YOU MUST ADDS MORE ROWS
COSTOS PRODUCTOS NACIONALES

Cell Formulas
Range	Formula
F88:F100	F88	=IFERROR(E88/D88,0)
G88:G100	G88	=C88
B88:B100	B88	=$B$3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:G100	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E88:E100	Any value

 

[leobrice]

If you do use the above, delete the Debug.Print line !!!

Thanks @Snakehips
I really like both options, I'm even leaning towards tables, but I have a problem. All the sheets in the file are protected and apparently the autocomplete table row process doesn't work on protected sheets. So the macro option would work for me keeping the sheets protected.
What I would like is to place after the last available row a row warning the end of available rows and that it will have to add 500 rows, then when executing the macro that row will go down 500 rows and it will be the last one again.

Translated with DeepL.com (free version)

Sorry, dont translated last post