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

 

[OaklandJim]

It is challenging to understand what you are asking for without knowing more.

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.

What "changes"? Changes based on what criteria or for what action? Do you mean if a record is deleted, added or modified in one worksheet it should be deleted, added or modified in the other?

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.

Again, deleting based on what or after what? Do you mean remove empty rows after records have been deleted?

Is data always located where shown?

I am guessing that you did not intend to show Invoice # in two columns in the data sheet?

In the "Monthly" worksheet you show records for more than one month?

 

[nmiller770678]

Hi OaklandJim.
A user can search for an invoice # (which is based off of column A invoice #) and make changes to the record which updates the record on the Data tab. I would like those same changes to be updated on all associated records on the Monthly tab. The user can also delete records from the Data tab, and I would like all associated rows on the Monthly tab to be deleted. Overall, the same data should be on both tabs just in different formats. Below is a screenshot of the actual user form; not sure if it's relevant.

Yes, the data will always be in the same columns for both Data and Monthly tabs.
Yes, the entire workbook will be used for data over years (approx. 1500-2500 records a year) so I will be adding a drop down for month and year that the user wants to report on and based on that information I would have another tab show the data in the format that they need.

The Monthly tab doesn't have to match at all times but maybe a separate button that the user pushes at the end of the month and all data on the Monthly tab is updated then? I'm still don't know how to get it into the format I need from the Data tab to the Monthly tab.

 

[OaklandJim]

So there may be one to four parts per row in the Data worksheet? Just so there are no surprises...Is the data in the Data worksheet actually organized and labeled exactly as shown (especially column headers) in the example?

 

[OaklandJim]

Is Invoice # in column A or C? I assume column C and Column A is empty?

DataToReportWorkbook.xlsm
	A	B	C
2	Invoice #	Date	Invoice #
3	654123	9/28/2022	654123
4	887765	9/28/2022	887765
5	223456	9/28/2022	223456
6	438542	2/1/2022	438542
7	231221	3/4/2022	231221
8	781236	5/1/2023	781236
9	521689	4/4/2023	521689
Data

 

[OaklandJim]

Oh man. In the data sheet there are eight columns per part #. In the Report there are 9 columns. And the columns are not in the same order. If the headers are not the same then I cannot think of a straightforward way to copy part # data into the Report for a row in the Data sheet.

DataToReportWorkbook2.xlsm
	K	L	M	N	O	P	Q	R	S
4	1	2	3	4	5	6	7	8	9
5	Part # 1	Prod Name 1	Registration 1	Volume 1	Lot# 1	Repack 1	Qty 1	Total 1
6
7	Part	Prod Name	Registration	Tot Vol/Qty	Qty 1	Repack?	Lot Number	Tot Vol/Qty	Group By
Report

Please advise: I sure hope that the columns for a part # are the same for the Data worksheet and the Report worksheet. Until I know otherwise I'll assume that the columns for a part # in the Report are the same as those in the Data.

DataToReportWorkbook2.xlsm
	K	L	M	N	O	P	Q	R	S
4	1	2	3	4	5	6	7	8	9
5	Part # 1	Prod Name 1	Registration 1	Volume 1	Lot# 1	Repack 1	Qty 1	Total 1
6
7	Part	Prod Name	Registration	Volume	Lot Number	Repack?	Qty 1	Total	Group By
Report

 

[nmiller770678]

So there may be one to four parts per row in the Data worksheet? Just so there are no surprises...Is the data in the Data worksheet actually organized and labeled exactly as shown (especially column headers) in the example?Y

Oh man. In the data sheet there are eight columns per part #. In the Report there are 9 columns. And the columns are not in the same order. If the headers are not the same then I cannot think of a straightforward way to copy part # data into the Report for a row in the Data sheet.

DataToReportWorkbook2.xlsm
	K	L	M	N	O	P	Q	R	S
4	1	2	3	4	5	6	7	8	9
5	Part # 1	Prod Name 1	Registration 1	Volume 1	Lot# 1	Repack 1	Qty 1	Total 1
6
7	Part	Prod Name	Registration	Tot Vol/Qty	Qty 1	Repack?	Lot Number	Tot Vol/Qty	Group By
Report

Please advise: I sure hope that the columns for a part # are the same for the Data worksheet and the Report worksheet. Until I know otherwise I'll assume that the columns for a part # in the Report are the same as those in the Data.

DataToReportWorkbook2.xlsm
	K	L	M	N	O	P	Q	R	S
4	1	2	3	4	5	6	7	8	9
5	Part # 1	Prod Name 1	Registration 1	Volume 1	Lot# 1	Repack 1	Qty 1	Total 1
6
7	Part	Prod Name	Registration	Volume	Lot Number	Repack?	Qty 1	Total	Group By
Report

Yes there can be up to 4 parts to a row on the data sheet but most often will be 1 or 2. The first post shows the sheets with the actual headers that I’m using.

Invoice # is listed twice because I started with it being in column C but realized I needed it in A. I didn’t want to have to change all the VBA I had already did so I duplicated it from C to A.

The registration, part name, qty, and volume can all be returned with a formula once the part number is put into the monthly report. This list is fairly small so that would be no issue.

At this point I am open to anything for the monthly report. I was toying with having a separate spreadsheet that is only updated at the end of the month when the user selects a reporting month and year then pushes a button. All data for the month and year selected would be returned to the spreadsheet in the below format (or any format at this point). This would alleviate the need for code to update any frequent changes….but I still can’t figure out how to copy the data into the report format below.

I was successful at using the filter function to return the invoice number and date in the first screenshot below but that’s where I got stuck again.

Thank you!!!

 

[OaklandJim]

Are you sure that you cannot get rid of the column A content? If you provide a link to your workbook I can try to fix your code.

Below is a link to the workbook that I set up. There is too much code to post. As is normal for me the code is a bit clunky but it works and only takes a second or so.

One reason that there is a lot of code is the four parts per row. That made the logic a bit convoluted.

Good luck! DataToReportWorkbook4.xlsm

 

[Fluff]

There is too much code to post

You still need to post all the code, as per Rule#4 thanks.

 

[OaklandJim]

Here is all code. Don't laugh!

Sub ConvertDataToReport()

'   -----------------------------
'           Declarations
'   -----------------------------

'   Worksheet objects 1. Data and 2. Report
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    
'   Range containing all data in the Report worksheet used to clear that data
'   before processing new data.
    Dim rAllData As Range
    
'   Data anchor cells for 1. Data worksheet, 2. Report worksheet.
    Dim rAnchorCellData As Range
    Dim rAnchorCellReport As Range
    
'   Header cells containing strings Part # 1, Part # 2 etc.
    Dim rHeaderPart1 As Range
    Dim rHeaderPart2 As Range
    Dim rHeaderPart3 As Range
    Dim rHeaderPart4 As Range
    
'   Used for iterating through a range of values.
    Dim rCell As Range
    
'   Count of rows in the Data
    Dim iDataRows As Long
    
'   Used for iterating through all data rows in the source worksheet.
    Dim iDataRow As Long

'   How many columns of data for each part #.
    Dim iDataColumnsPerPart As Long
    
'   There are this many columns of invoice data for each data row in data worksheet.
    Dim iInvoiceColumnsCount As Long
    
'   Used for iterating parts for a data row.
    Dim iPartNum As Long
    
'   Count of parts for a given data row
    Dim iPartsCountForARow As Long
    
'   While iterating all Data rows, keep track of how many Report rows have been added
    Dim iReportRowsProcessed As Long
    
'   Used to iterate through all rows in the Report wotksheet.
    Dim iReportRow As Long
    
'   Used for messaging.
    Dim sMsg As String
    
'   -----------------------------
'           Initializations
'   -----------------------------

'   The set wsData worksheet object to data worksheet
    Set wsData = ThisWorkbook.Worksheets("Data")
    
'   The set wsReport worksheet object to Report worksheet
    Set wsReport = ThisWorkbook.Worksheets("Report")
    
'   Find first cell containing the Text "Date" in Data worksheet.
    Call FindStringInSheet(wsData, "Date", rCell)
    
    If rCell Is Nothing _
     Then
        sMsg = "Unable to find the anchor cell" _
             & Chr(10) _
             & "for data in Data worksheet." _
             & Chr(10) _
             & "Looking for the word Date."
        MsgBox sMsg, vbCritical, "Processing data in Data Worksheet."
        Exit Sub
    Else
        Set rAnchorCellData = rCell
    End If

'   Find first cell containing data in Report worksheet including headers.
'   Looking for the word Date, case sensitive.
    Call FindStringInSheet(wsReport, "Date", rCell)
    
    If rCell Is Nothing _
     Then
        sMsg = "Unable to find the anchor cell" _
             & Chr(10) _
             & "for data in Report worksheet." _
             & Chr(10) _
             & "Looking for the word Date."
        MsgBox sMsg, vbCritical, "Processing data for Report Worksheet."
        Exit Sub
    Else
        Set rAnchorCellReport = rCell
    End If
    
'   Last occupied data row in the Data worksheet.
    iDataRow = rAnchorCellData.Offset(1000000).End(xlUp).Row
    
'   There are this many rows of data in the Data worksheet (not including headers).
    iDataRows = iDataRow - rAnchorCellData.Row
    
'   There are this many columns of data for each part # in the Data worksheet (AND report worksheet).
'   Columns 1. Part #, 2. Prod Name, 3. Registration, 4. Volume 5. Lot#, 6. Repack, 7. Qty, 8. Total

    iDataColumnsPerPart = 8
    
'   There are this many columns of data for each invoice inthe data worksheet (AND report worksheet).
'   Columns 1. Date, 2. Invoice #, 3. Cust ID, 4. Ship To State, 5. Prepared By, 6. Exporting To.
    iInvoiceColumnsCount = 6
        
'   ----------------------------------
'        Sort Data Based on Date
'   ----------------------------------

'   Set range object to point to all data in Data worksheet including headers'
    Set rAllData = rAnchorCellData.CurrentRegion
    
'   Sort data in ascending order, based on the column with CUSTNMBR values.
    rAllData.Sort Key1:=rAnchorCellData, _
                     Order1:=xlAscending, _
                     Header:=xlYes
                   
'   ----------------------------------------------
'        Setup Part-specific Anchor Cells (4)
'   ----------------------------------------------

'   Find each Part # header -- one though four (i.e., max of four parts per data line).
    For iPartNum = 1 To 4
    
        Call FindStringInSheet(wsData, "Part # " & iPartNum, rCell)
        
        If Not rCell Is Nothing _
         Then
            If iPartNum = 1 Then Set rHeaderPart1 = rCell
            If iPartNum = 2 Then Set rHeaderPart2 = rCell
            If iPartNum = 3 Then Set rHeaderPart3 = rCell
            If iPartNum = 4 Then Set rHeaderPart4 = rCell
        End If

    Next iPartNum

'   ----------------------------------------
'        Clear Existing Data in Report
'   ----------------------------------------

'   Clear 1000 rows of data. Width of range to clear is iInvoiceColumnsCount + iDataColumnsPerPart + 1.
    rAnchorCellReport.Offset(1).Resize(1000, iInvoiceColumnsCount + iDataColumnsPerPart + 1).Clear

'   ----------------------------------------
'        Process Invoice Data Columns
'   ----------------------------------------
    
    iReportRowsProcessed = 0
    
'   Iterate through all data rows in the Data worksheet. Put Invoice data into the Report worksheet.
    For iDataRow = 1 To iDataRows
    
'       How many parts in the row being processed? Use function CountPartsInRow.
        iPartsCountForARow = CountPartsInRow(iDataRow, rHeaderPart2, rHeaderPart3, rHeaderPart4)
        
'       For each part in the respective data row, copy invoice data in Data worksheet then
'       paste those values (columns) into the Report worksheet in the invoice data columns.
        For iReportRow = 1 To iPartsCountForARow
            iReportRowsProcessed = iReportRowsProcessed + 1
            rAnchorCellData.Offset(iDataRow).Resize(1, iInvoiceColumnsCount).Copy _
            Destination:=rAnchorCellReport.Offset(iReportRowsProcessed)
        Next iReportRow
            
    Next iDataRow
    
'   ----------------------------------------
'         Process Parts Data Columns
'   ----------------------------------------
    
    iReportRowsProcessed = 0

'   Iterate through all data rows in the Data worksheet. Put parts data into the Report worksheet.
    For iDataRow = 1 To iDataRows

'       How many parts in the row being processed? Use function CountPartsInRow.
        iPartsCountForARow = CountPartsInRow(iDataRow, rHeaderPart2, rHeaderPart3, rHeaderPart4)
            
'       ---------------------------------------------------------
'             Process Parts Data Columns for Each Data Row
'       ---------------------------------------------------------

'       Process parts for data for the Report worksheet.
        For iReportRow = 1 To iPartsCountForARow
        
'           Copy Part # 1 data to the Report.
            If iReportRow = 1 _
             Then
                iReportRowsProcessed = iReportRowsProcessed + 1
                rHeaderPart1.Offset(iDataRow).Resize(1, iDataColumnsPerPart).Copy _
                Destination:=rAnchorCellReport.Offset(iReportRowsProcessed, iInvoiceColumnsCount)
            End If

'           If there is one then copy Part # 2 data to the Report.
            If iReportRow = 2 _
             Then
                iReportRowsProcessed = iReportRowsProcessed + 1
                rHeaderPart2.Offset(iDataRow).Resize(1, iDataColumnsPerPart).Copy _
                Destination:=rAnchorCellReport.Offset(iReportRowsProcessed, iInvoiceColumnsCount)
            End If

'           If there is one then copy Part # 3 data to the Report.
            If iReportRow = 3 _
             Then
                iReportRowsProcessed = iReportRowsProcessed + 1
                rHeaderPart3.Offset(iDataRow).Resize(1, iDataColumnsPerPart).Copy _
                Destination:=rAnchorCellReport.Offset(iReportRowsProcessed, iInvoiceColumnsCount)
            End If

'           If there is one then copy Part # 4 data to the Report.
            If iReportRow = 4 _
             Then
                iReportRowsProcessed = iReportRowsProcessed + 1
                rHeaderPart4.Offset(iDataRow).Resize(1, iDataColumnsPerPart).Copy _
                Destination:=rAnchorCellReport.Offset(iReportRowsProcessed, iInvoiceColumnsCount)
            End If
        
        Next iReportRow
        
    Next iDataRow
    
End Sub

' ----------------------------------------------------------------
' Procedure Name: CountPartsInRow
' Purpose: Counts number of parts in a row in data in the Data worksheet.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter piDataRow (Long): Current row being processed.
' Parameter prAnchor2 (Range): "Anchor cell" for Part # 2
' Parameter prAnchor3 (Range): "Anchor cell" for Part # 3
' Parameter prAnchor4 (Range): "Anchor cell" for Part # 4
' Return Type: Long
' Author: Jim
' Date: 10/11/2022
' ----------------------------------------------------------------

Function CountPartsInRow( _
    piDataRow As Long, _
    prAnchor2 As Range, _
    prAnchor3 As Range, _
    prAnchor4 As Range) _
As Long

'   How many parts in the row being processed?

'   There is at least one part per row.
    CountPartsInRow = 1
    
'   Look in the column cotaining Part # 2 for the data row piDataRow.
'   If not empty then there is a part #2.
    If prAnchor2.Offset(piDataRow).Value <> "" Then CountPartsInRow = 2
    
'   Look in the column cotaining Part # 3 for the data row piDataRow.
'   If not empty then there is a part #3.
    If prAnchor3.Offset(piDataRow).Value <> "" Then CountPartsInRow = 3
    
'   Look in the column cotaining Part # 4 for the data row piDataRow.
'   If not empty then there is a part #4.
    If prAnchor4.Offset(piDataRow).Value <> "" Then CountPartsInRow = 4

End Function

Function FindStringInSheet(pwsSheet As Worksheet, psFindString As String, Optional ByRef prCell) As String
    
    Dim rCellFound As Range
    
    Set rCellFound = Nothing
    
    FindStringInSheet = ""
    
    On Error Resume Next
    Set rCellFound = pwsSheet.Cells.Find(What:=psFindString, LookAt:=xlWhole)
    On Error GoTo 0
    
    If Not rCellFound Is Nothing _
     Then
        If Not IsMissing(prCell) Then Set prCell = rCellFound
        FindStringInSheet = rCellFound.Address
    End If
    
End Function