copy and paste rows in excel to another worksheet vba using input box for the amount of rows to copy

[Evie76]

Hi, I'm new to VBA and have been trying to find a code that will transfer data from sheet 1 to sheet 2 using an input box on the amount of rows to copy over.

Sheet 1 is where the user will input the invoice information from D6 to P6, once the information has been checked it would then be copied over to sheet 2 starting in column b and ending in column n. The input box would be used to state how many rows to copy over as the invoices vary. Is there a way for this to be done via VBA. Any help would be greatly appreciated.

 

[kokano90]

Hi, I'm new to VBA and have been trying to find a code that will transfer data from sheet 1 to sheet 2 using an input box on the amount of rows to copy over.

Sheet 1 is where the user will input the invoice information from D6 to P6, once the information has been checked it would then be copied over to sheet 2 starting in column b and ending in column n. The input box would be used to state how many rows to copy over as the invoices vary. Is there a way for this to be done via VBA. Any help would be greatly appreciated.

Can you attach or describe a bit more. If possible attach file. I think I can help you

 

[Evie76]

Template Invoices - Copy.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P
1		LTS Invoice	Rate Invoice	Sub Value Inv			LTS Sheet	Rate	Value on sheet	LTS & Rate Together
2			0.32				64,683		£20,698.56	0
3
4
5	Invoice Date	Batch No	Original Ref	Measure_Reference_Number	Measure_Type	Cost_Score_(£)	Rate Value	Amount (£)	1st line	Town	Post_Code	Notes
6	03/06/2021	641 - YESSub122	UTA6001783	UTA7003543	Heating_controls_solid	1,931	£0.32	£617.92	1 Cherry Tree Lane	London	NW33 4QQ
7	03/06/2021	641 - YESSub122	UTA6001782	UTA7003542	B_Broken_solid_nopreHCs	10,099	£0.32	£3,231.68	2 Cherry Tree Lane	London	NW33 4QQ
8	03/06/2021	641 - YESSub122	UTA6001781	UTA7003541	UFI	4,284	£0.32	£1,370.88	3 Cherry Tree Lane	London	NW33 4QQ
9	03/06/2021	641 - YESSub122	UTA6001777	UTA7003540	LI_lessequal100	1,981	£0.32	£633.92	4 Cherry Tree Lane	London	NW33 4QQ
10	03/06/2021	641 - YESSub122	UTA6001778	UTA7003539	UFI	2,178	£0.32	£696.96	5 Cherry Tree Lane	London	NW33 4QQ
11	03/06/2021	641 - YESSub122	UTA6001798	UTA7003538	Compensation_cavity_nopreHCs	265	£0.32	£84.80	6 Cherry Tree Lane	London	NW33 4QQ
12	03/06/2021	641 - YESSub122	UTA6001797	UTA7003537	Heating_controls_cavity	1,167	£0.32	£373.44	7 Cherry Tree Lane	London	NW33 4QQ
13	03/06/2021	641 - YESSub122	UTA6001796	UTA7003536	B_Broken_cavity_nopreHCs	6,061	£0.32	£1,939.52	8 Cherry Tree Lane	London	NW33 4QQ
14	03/06/2021	641 - YESSub122	UTA6001795	UTA7003535	UFI	2,178	£0.32	£696.96	9 Cherry Tree Lane	London	NW33 4QQ
15	03/06/2021	655 - YESSub127	UTA6001816	UTA7003636	Compensation_cavity_nopreHCs	265	£0.32	£84.80	10 Cherry Tree Lane	London	NW33 4QQ
16	03/06/2021	655 - YESSub127	UTA6001815	UTA7003635	Heating_controls_cavity	1,167	£0.32	£373.44	11 Cherry Tree Lane	London	NW33 4QQ
17	03/06/2021	655 - YESSub127	UTA6001780	UTA7003634	Smarttherm_cavity_nopreP&RT	932	£0.32	£298.24	12 Cherry Tree Lane	London	NW33 4QQ
18	03/06/2021	655 - YESSub127	UTA6001813	UTA7003633	Compensation_cavity_nopreHCs	171	£0.32	£54.72	13 Cherry Tree Lane	London	NW33 4QQ
19	03/06/2021	655 - YESSub127	UTA6001812	UTA7003632	Heating_controls_cavity	692	£0.32	£221.44	14 Cherry Tree Lane	London	NW33 4QQ
20	03/06/2021	655 - YESSub127	UTA6001811	UTA7003631	B_First_time_CH_cavity	6,546	£0.32	£2,094.72	15 Cherry Tree Lane	London	NW33 4QQ
21	03/06/2021	655 - YESSub127	UTA6001823	UTA7003630	UFI	2,398	£0.32	£767.36	16 Cherry Tree Lane	London	NW33 4QQ
22	03/06/2021	647 - YESSub125	UTA6001814	UTA7003605	B_Broken_cavity_nopreHCs	12,123	£0.32	£3,879.36	17 Cherry Tree Lane	London	NW33 4QQ
23	03/06/2021	647 - YESSub125	UTA6001779	UTA7003604	B_Broken_cavity_nopreHCs	10,245	£0.32	£3,278.40	18 Cherry Tree Lane	London	NW33 4QQ
24
25
26
Without

Cell Formulas
Range	Formula
K2,M2	K2	=SUM(J6:J46)
N2	N2	=K2*L2
L6:L23	L6	=J6*K6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2,N2	Cell Value	duplicates	text	NO
H2,M2	Cell Value	duplicates	text	NO
G2,L2	Cell Value	duplicates	text	NO
F2,K2	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2	List	0.10,0.32
L2	List	0.10,0.32
P6:P26	List	ECOHub states measures as being Heating Control

Template Invoices - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Installer	Invoice No	Invoice Date	Batch No	Original Ref	Ofgem Ref	Measure Category	LTS	Rate Value	Amount	First Line of Address	Area	Postcode	Notes
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Master

 

[mumps]

Once you enter the number of rows to copy, on which row do you want to start copying?

 

[Evie76]

the next available row on sheet 2, master tab

 

[mumps]

I meant from which row on the "Without" sheet do you want to start copying? The data starts in Row 6. So if you enter that you want to copy 5 rows rows, do you copy rows 6 to 10?

 

[Evie76]

I meant from which row on the "Without" sheet do you want to start copying? The data starts in Row 6. So if you enter that you want to copy 5 rows rows, do you copy rows 6 to 10?

From row 6 to the row the user has inputted to so it can be 1, 5, 11, etc as the invoices vary on the amount of measures that is on there

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter the number of rows to copy.")
    If response = "" Then Exit Sub
    Sheets("Without").Range("B6:N6").Resize(response).Copy Sheets("Master").Cells(Sheets("Master").Rows.Count, "B").End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

Change the sheet names (in red) to suit your needs.

 

[Evie76]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter the number of rows to copy.")
    If response = "" Then Exit Sub
    Sheets("Without").Range("B6:N6").Resize(response).Copy Sheets("Master").Cells(Sheets("Master").Rows.Count, "B").End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

Change the sheet names (in red) to suit your needs.

This works, however, if I delete the last row in the master sheet when I copy over the next invoice it misses the deleted row, and goes to the next one, if there a way for it not to do that and just copy on the next blank row

 

[Evie76]

This works, however, if I delete the last row in the master sheet when I copy over the next invoice it misses the deleted row, and goes to the next one, if there a way for it not to do that and just copy on the next blank row

I've worked it out. Thank you so much for your help, i really appreciate it