Guestimator
New Member
- Joined
- Apr 28, 2016
- Messages
- 6
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.
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.
VBA Code:
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