How to update data that is on two worksheets but different formats

[nmiller770678]

I have spreadsheet with a tab named Data and another named Monthly.
I have a User form that inputs the data into both these tabs with no issues.
The user form also allows for the updating of data as well as deleting of part#'s or entire records on the Data tab.
The issue I'm having is I haven't been able to get any code to work that will update changes to both the Data tab records and the Monthly records.
I am also looking for code that would delete records on both tabs but then move the remaining data so that there aren't any blank rows.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1
2	Invoice #	Date	Invoice #	Cust ID	Ship To State	Prepared By	Exporting To?	Part # 1	Prod Name 1	Registration 1	Volume 1	Lot# 1	Repack 1	Qty 1	Total 1	Part # 2	Prod Name 2	Registration 2	Volume 2	Lot# 2	Repack 2	Qty 2	Total 2	Part # 3	Prod Name 3	Registration 2	Volume 3	Lot # 3	Repack 3	Qty 4	Total 3	Part # 4	Prod Name 4	Registration3	Volume 4	Lot # 4	Repack 4	Qty 4	Total 4	Notes
3	654123	9/28/2022	654123	KHK	FL	HJH		1000-01	Red Sneaker	665544-0	Formula	123	Original Order	1	Formula	1000-04	Red Sneaker w/Stripe	665520-0	Formula	333	Reorder	1	Formula	2000-35A	Black Sneaker	665543-0	Formula	444	Reporder	1	Formula				Formula				Formula	Notes do not save on Monthly tab
4	887765	9/28/2022	887765	KH	AL	KH		1000-01	Red Sneaker	665544-0	Formula	123	Original Order	1	Formula
5	223456	9/28/2022	223456	MYL	BB	NM		3000-01	Blue Sneaker	665545-0	Formula	555	No Reorder	1	Formula
6	438542	2/1/2022	438542	USH		KA	Canada	4000-05	Sandal	55456-0	Formula	777	Original Order	1	Formula	4001-06	Sandal w/Black Sole		Formula	321	Reorder	1	Formula
7	231221	3/4/2022	231221	QFY	AL	ML		3002-02	Black Boots		Formula	222	Reorder	2	Formula
8	781236	5/1/2023	781236	CCK	DE	ML		5622-01	Trainer			456	Original Order	1	Formula	5622-04	Trainer Multi		Formula	789	No Reorder	1	Formula	3451-01	Boots Ankle		Formula	654	No Reorder	1	Formula	3002-02	Black Boots		Formula	222	Reorder	2	Formula	Notes do not save on Monthly tab
9	521689	4/4/2023	521689	LSY		CA	Canada	4000-05	Sandal	55456-0	Formula	777	Original Order	2	Formula
10
Data

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3
4
5																		Formula	Formula
6																		Doesn't copy
7					Date	Invoice#	Cust ID	Ship To State	Prepared By	Exporting To?	Part	Prod Name	Registration	Volume	Qty 1	Repack?	Lot Number	Tot Vol/Qty	Group By
8					9/28/2022	654123	KHK	FL	HJH		1000-01	Red Sneaker	665544-0		1	Original Order	123	2	Sneaker
9					9/28/2022	654123	KHK	NM	HJH		1000-04	Red Sneaker w/Stripe	665520-0		1	Reorder	333	2	Sneaker
10					9/28/2022	654123	KHK	AZ	HJH		2000-35A	Black Sneaker	665543-0		1	Reorder	444	3	Sneaker
11					9/28/2022	887765	KH	AL	KH		1000-01	Red Sneaker	665544-0		1	Original Order	123	1	Sneaker
12					9/28/2022	223456	MYL	BB	NM		3000-01	Blue Sneaker	665545-0		1	No Reorder	555	2	Sneaker
13					2/1/2022	438542	USH		KA	Canada	4000-05	Sandal	55456-0		1	Original Order	777	1	Sandal
14					2/1/2022	438542	USH		KA	Canada	4001-06	Sandal w/Black Sole			1	Reorder	321	1	Sandal
15					3/4/2022	231221	QFY	AL	ML		3002-02	Black Boots			2	Reorder	222	4	Boots
16					5/1/2023	781236	CCK	DE	ML		5622-01	Trainer			1	Original Order	456	1	Trainer
17					5/1/2023	781236	CCK	DE	ML		5622-04	Trainer Multi			1	No Reorder	789	1	Trainer
18					5/1/2023	781236	CCK	DE	ML		3451-01	Boots Ankle			1	No Reorder	654	1	Boots
19					5/1/2023	781236	CCK	DE	ML		3002-02	Black Boots			2	Reorder	222	4	Boots
20					4/4/2023	521689	LSY		CA	Canada	4000-05	Sandal	55456-0		2	Original Order	777	4	Sandal
21
22
23
Monthly

 

[nmiller770678]

This is wonderful! I'll put it into the existing file later today or tomorrow and run some tests.
As for Invoice # in column A below is part of the code that references A for the monthly but I also use it for searching records. If I'm not mistaken I couldn't get the search to look at what is now invoice in col. C so I duplicate it in A.

Sheets("Monthly").Select


Set sh = ThisWorkbook.Sheets("Monthly")
lr = Sheets("Monthly").Range("A" & Rows.Count).End(xlUp).Row


'''''''''''Add data in Excel Sheet named Monthly - this is a different format than the Data tab'''''''''''


With sh
.Cells(lr + 1, "A").Value = Me.tbInvoice.Value
.Cells(lr + 1, "E").Value = Me.tbDate.Value
.Cells(lr + 1, "F").Value = Me.tbInvoice.Value
.Cells(lr + 1, "G").Value = Me.tbCust.Value
.Cells(lr + 1, "H").Value = Me.tbShipTo.Value
.Cells(lr + 1, "I").Value = Me.tbPrepared.Value
.Cells(lr + 1, "J").Value = Me.tbExport.Value

.Cells(lr + 1, "K").Value = Me.cbPN1.Value
.Cells(lr + 1, "L").Value = Me.tbPN1PN.Value
.Cells(lr + 1, "M").Value = Me.tbEPAReg1.Value
'.Cells(lr + 1, "N").Value = Me.tbVol1.Value
.Cells(lr + 1, "O").Value = Me.tbQty1.Value

.Cells(lr + 1, "P").Value = Me.cbRepack1.Value
.Cells(lr + 1, "Q").Value = Me.tbLot1.Value


End With

 

[nmiller770678]

I assure you I have limited VBA skills and my code is all over the place but it works and doesn't take forever to run! : )

 

[nmiller770678]

I'm clearly not doing something right....I've attempted to add your code to my existing document. It will save the record in both the Data and the Reports tab but it's very slow (assuming a combination of both codes together) and as far as updating records it won't do that at all.
I tried adding Call ConvertDataToReport and Call DeleteEmptyRows_Data to Private Sub cmdUpdate_Click() and Private Sub cmdSave_Click()

 

[nmiller770678]

I have a file that I've removed proprietary information from if you need me to send it to you.

 

[OaklandJim]

Sure, provide a link to the workbook. I'll add code to it. FYI I sort data by date. One effect is that empty rows are sorted to the bottom of the list which has the same effect as "deleting" the empty rows. So sub DeleteEmptyRows_Data is not needed/used. Also, the sub runs in about one second on my old pc.