Macro to clear data, copy new data and eliminate INDEX/MATCH formulas

[Guestimator]

I have a workbook with three tabs, NEUT, WORK and DBASE. I work in the WORK tab which uses INDEX/MATCH functions to pull information from the database. When I have an estimate completed I need to remove the index/match functions and leave the other formulas performing various calculations. I currently save the file as neutralized and copy and paste values for each column with the index/match formula. The columns with INDEX/Match are B:E,G,J,L,N,P,AA;BE. I added the NEUT tab and thought I could copy information from the WORK tab with all of the formatting and then remove the INDEX/MATCH functions by copy and paste value. I have 8900 rows in the NEUT tab so that I don't copy over the formulas at the bottom of the sheet. If I can get this to work smoothly I will not need to have multiple files for one project and it will save a lot of time. Since the WORK tab gets updated often I will need to be able to run this macro often. I labeled a cell in "A:A" below the totals, "COPY" that is a blank row with all formula and formatting. I also labeled a cell in "A:A" at the totals "END"

1. Clear existing data from NEUT tab. My thought was to copy a formatted row, labeled "COPY" in "A:A" and paste from row 10 to "END"-2 label in "A:A"
2. Copy all data from WORK tab row 10 to "END"
3. Copy columns B:E,G,J,L,N,P,AA;BE with INDEX/MATCH and paste as value.

Is there a way to make sure there are enough rows in the NEUT tab prior to copying the WORK tab so that I don't accidently copy over the total formulas at the bottom of the NEUT tab or is it better to leave the sheet with all those rows? I do not know how to write this code. I recorded the code below but it only copies to row 188, I need this to be variable since every estimate is a different # rows.

Sub NEUTRALIZER()
'
' NEUTRALIZER Macro
'

'
    Rows("8924:8924").Select
    Selection.Copy
    Rows("10:8916").Select
    Range("A8916").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-24
    Sheets("WORK").Select
    Rows("9:187").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("NEUT").Select
    Rows("10:10").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=63
    ActiveWindow.LargeScroll Down:=12
    ActiveWindow.ScrollRow = 803
    ActiveWindow.ScrollRow = 779
    ActiveWindow.ScrollRow = 755
    ActiveWindow.ScrollRow = 611
    ActiveWindow.ScrollRow = 466
    ActiveWindow.ScrollRow = 334
    ActiveWindow.ScrollRow = 298
    ActiveWindow.ScrollRow = 201
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 9
    Range("B10:E188").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G10:G188").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J10:J188").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L10:L188").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N10:N188").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P10:P188").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AA10:BE188").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[pbornemeier]

It would probably be easiest to recreate the formulas at the bottom of the data rows and the required formatting throughout the data and total in code.
Is the last row in B:BE a sum formula for row 10 to the row above the formula row? If not, what formulas are in what column?

Does the last row of the WORK worksheet contain the same formulas?

Could you use Excel Genie (see link in my sig) or another program to post some portions of your worksheets.

This piece of code replaces the many Copy/PasteSpecial blocks at the end of your post.

Sub Partial()

    Dim lCOPYRow As Long
    Dim lADDRow As Long
    Dim aryIndexMatchColumns As Variant
    Dim lIMColIndex As Long
    Dim lActiveColumn As Long
    
    aryIndexMatchColumns = Array(7, 10, 12, 14, 16)
    '                            G   J   L   N   P
    Dim lLastCopyRow As Long
    
    lLastCopyRow = 188
    With Range("B10:E" & lLastCopyRow)
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End With
    
    For lIMColIndex = LBound(aryIndexMatchColumns) To UBound(aryIndexMatchColumns)
        lActiveColumn = aryIndexMatchColumns(lIMColIndex)
        With Range(Cells(10, lActiveColumn), Cells(lLastCopyRow, lActiveColumn))
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        End With
    Next
    
    With Range("AA10:BE" & lLastCopyRow)
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End With
    

End Sub

 

[Guestimator]

Yes, the last row sums all data from row 7 down.
Yes, the WORK worksheet contains the same sum formulas.
The NEUT worksheet is a copy of the WORK worksheet without any IndexMatch formulas.
Would it be easier to copy the WORK worksheet complete to the NEUT worksheet and then remove the IndexMatch formulas from the NEUT worksheet.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	PROJECT:	TEST MACRO
2	DATE:	1/19/2020
3	USER:
4								L-RATE	$50.00
5
6	CODE		DESCRIPTION	QTY	UM	T-MH	MH/U	L-UNIT	L-TOTAL	M-UNIT	M-TOTAL	E-UNIT	E-TOTAL	S-UNIT	S-TOTAL	R-UNIT	R-TOTAL	TOTAL
7
8
9			PROJECT NAME
10	A		FOYER	100.0	SF	12.00	0.120	6.00	600	100.00	10,000	5.25	525	18.50	1,850	-	-	12,975
11	B		STUDY	100.0	SF	25.00	0.250	12.50	1,250	110.00	11,000	5.25	525	18.50	1,850	-	-	14,625
12	C		LOUNGE	100.0	SF	25.00	0.250	12.50	1,250	120.00	12,000	5.25	525	18.50	1,850	-	-	15,625
13	D		BEDRROM 1	100.0	SF	30.00	0.300	15.00	1,500	130.00	13,000	5.25	525	18.50	1,850	-	-	16,875
14	E		BEDROOM 2	100.0	SF	30.00	0.300	15.00	1,500	140.00	14,000	5.25	525	18.50	1,850	-	-	17,875
15	F		BEDROOM 3	100.0	SF	30.00	0.300	15.00	1,500	150.00	15,000	5.25	525	18.50	1,850	-	-	18,875
16	G		BATH 1	100.0	SF	50.00	0.500	25.00	2,500	160.00	16,000	5.25	525	18.50	1,850	-	-	20,875
17	H		BATH 2	100.0	SF	50.00	0.500	25.00	2,500	170.00	17,000	5.25	525	18.50	1,850	-	-	21,875
18	I		BATH 3	100.0	SF	50.00	0.500	25.00	2,500	180.00	18,000	5.25	525	18.50	1,850	-	-	22,875
19	J		FAMILY ROOM	100.0	SF	20.00	0.200	10.00	1,000	190.00	19,000	5.25	525	18.50	1,850	-	-	22,375
20	K		KITCHEN	100.0	SF	60.00	0.600	30.00	3,000	200.00	20,000	5.25	525	18.50	1,850	-	-	25,375
21
22	END
23			TOTALS			382			19,100		165,000		5,775		20,350		0	210,225
24
WORK

Cell Formulas
Range	Formula
E10, E13:E20, E11:E12	E10	=INDEX(VEACH,MATCH($A10,VCODE,0))
F10, F13:F20, F11:F12	F10	=G10*D10
H10:H20	H10	=G10*I$4
I10:I20	I10	=H10*D10
J10:J20	J10	=INDEX(VMATLU,MATCH($A10,VCODE,0))
K10:K20	K10	=D10*J10
L10:L20	L10	=INDEX(VSUBU,MATCH($A10,VCODE,0))
M10:M20	M10	=D10*L10
N10:N20	N10	=INDEX(VEQPU,MATCH($A10,VCODE,0))
O10:O20	O10	=D10*N10
P10:P20	P10	=INDEX(VOTHU,MATCH($A10,VCODE,0))
Q10:Q20	Q10	=D10*P10
R10:R20	R10	=SUM(I10,K10,M10,O10,Q10)
G11:G12	G11	=INDEX(VMHU,MATCH($A11,VCODE,0))
C10:C20	C10	=INDEX(VDESC,MATCH($A10,VCODE,0))
F23, Q23:R23, O23, M23, K23, I23	F23	=SUM(F7:F22)