Xlookup reference is shifting over each time after I use Macro
Our take
Hey everyone,
I have my main table where I want to display the cleaned up data.
Essentially I'm using xlookup references from sheet two.
When I run my macro in sheet two, for some reason my xlookup on sheet 1 moves over every single time.
For example... this is what the xlookup formula looks at before vs after the Macro
Before Macro - =XLOOKUP(A3,MTD!$A$3:$A$15,MTD!$B$3:$H$15)
After Macro - =XLOOKUP(A3,MTD!$A$3:$A$15,MTD!$B$3:$F$15)
Why would it move from H15 to F15? I'm a little lost why that would be.
Sub Macro5()
'
' Macro5 Macro
'
'
Range("A2").Select
ActiveCell.FormulaR1C1 = "Market"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Date"
Range("E2:J16").Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Range("A2:H2").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Windows("Overview Bullet-MTD.csv").Activate
Application.Left = 1551.25
Application.Top = 147.25
Windows("Weekly Exec Updates - Lead Pacing (Working File) (1).xlsx").Activate
Range("A20").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "MO"
Range("A21").Select
ActiveCell.FormulaR1C1 = "LA"
Range("A22").Select
ActiveCell.FormulaR1C1 = "AR"
Range("A23").Select
ActiveCell.FormulaR1C1 = "MS"
Range("B20").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-17]C[-1]:R[-4]C[-1],R[-17]C:R[-4]C[6])"
Range("B20").Select
Selection.AutoFill Destination:=Range("B20:B23"), Type:=xlFillDefault
Range("B20:B23").Select
Range("B21").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-18]C[-1]:R[-5]C[-1],R[-18]C:R[-5]C[6])"
Range("B22").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-19]C[-1]:R[-6]C[-1],R[-19]C:R[-6]C[6])"
Range("B23").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[6])"
Range("B3:B16").Select
Selection.NumberFormat = "m/d/yyyy"
Range("E3:E16").Select
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0%"
Range("G3:G16").Select
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0%"
Range("C20:D23").Select
Selection.NumberFormat = "#,##0"
Range("F20:F23").Select
Selection.NumberFormat = "#,##0"
Range("G20:G23").Select
Selection.Style = "Percent"
Sheets("YTD").Select
Windows("Overview Bullet-YTD.csv").Activate
Windows("Weekly Exec Updates - Lead Pacing (Working File) (1).xlsx").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("M2:M16").Select
Selection.Cut
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("D2").Select
ActiveSheet.Paste
Range("D2").Select
ActiveCell.FormulaR1C1 = "Actual Leads"
Range("K2:K16").Select
Selection.Cut
Range("E2").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("L2:L16").Select
Selection.Cut
Range("F2").Select
ActiveSheet.Paste
Range("H2:H16").Select
Selection.Cut
Range("G2").Select
ActiveSheet.Paste
Range("I2:V17").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2:G2").Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
Range("A23").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "MO"
Range("A24").Select
ActiveCell.FormulaR1C1 = "LA"
Range("A25").Select
ActiveCell.FormulaR1C1 = "AR"
Range("A26").Select
ActiveCell.FormulaR1C1 = "MS"
Range("B23").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[5])"
Range("B23").Select
Selection.AutoFill Destination:=Range("B23:B26"), Type:=xlFillDefault
Range("B23:B26").Select
Range("B24").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-21]C[-1]:R[-8]C[-1],R[-21]C:R[-8]C[5])"
Range("B25").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[5])"
Range("B25").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-22]C[-1]:R[-9]C[-1],R[-22]C:R[-9]C[5])"
Range("B26").Select
ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-1],R[-23]C[-1]:R[-10]C[-1],R[-23]C:R[-10]C[5])"
Range("A2:G16").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$G$16").AutoFilter Field:=1, Criteria1:=Array("AL", _
"CA", "HI", "NM", "NNE", "OHWVKY", "PA", "TW", "TX"), Operator:=xlFilterValues
Range("A3").Select
ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"A2:A16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("MTD").Select
Range("A2:H16").Select
Selection.AutoFilter
Range("A3").Select
ActiveSheet.Range("$A$2:$H$16").AutoFilter Field:=1, Criteria1:=Array("AL", _
"CA", "HI", "NM", "NNE", "OHWVKY", "PA", "TW", "TX"), Operator:=xlFilterValues
ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"A2:A16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Need macro advice! Saving longer numbers as strings to show all the charactersDesktop, Excel 365 Build 19929.20106 Click-to-Run, Beginner We have a macro that generates new files using a list of data. Sometimes that data is completely numerical with over 15 digits. Excel keeps reformatting these numerical entries using scientific notation, which screws up the output files. Right now I have to manually check the output files and reformat the affected ones so that all then hidden characters are shown. Can someone please advise on how I can update the macro to automatically convert these numbers into strings that show all the characters? The data is of variable length, but I'm pretty sure it's always under 20 characters. This problem only affects one cell of the output file. Everything else is generated without issue. It's basically the problem of "how do I make Excel macro copy-paste the entire FedEx tracking number correctly" but the data isn't consistently 16 characters and is frequently alphanumeric. EDIT: I'm open to even stupid and booleg solutions like "add a hidden text character before transferring the data" to force this to work. The output files are basically just printables. I just don't know anything about macros and do not have time to learn. submitted by /u/Umklopp [link] [comments]
- Am I not using XLOOKUP correctly? And absolutes $shttps://imgur.com/a/o0alvvv I am basically trying to get column "E" (Billing End) on the "Upload sheet" to populate with the values found in column "C" on the "Data sheet"...ONLY IF the values in Data Sheet (B:B) and Upload Sheet (C:C) match AS WELL AS Data Sheet (D:D) and Upload Sheet (B:B) match. Sorry, I could have organized the columns more alphabetically but I figure you'd get the gist of it and I can play around with the figures. In Upload sheet E2 and pulling formula down, I would put... XLOOKUP(1, ('Data sheet'!D$2:D$7=B2)*('Data sheet'!B$2:B$7=C2), 'Data sheet'!C$2:C$7, "" ) submitted by /u/jwnsfw [link] [comments]
- Links to a cell within current worksheet keep changing the location, or doesn't work at all.I have a series of titles at the top of my worksheet with links to the most popular categories, hopefully to get to those categories quickly for data entry. I right click on the cell where I want the link, select Link, the enter the cell reference I want to jump to, with the text to display. This works. It presents a link, and if i click on the link, it jumps to exactly where I want it to go. However, over time, the cell reference isn't correct, and jumps to something else. I assume this is happening due to modifications of the worksheet or what it's from. I do not use "$" in the cell reference, assuming that if I enter A720 as the cell reference, the reference will automatically be modified to A730 if 10 rows are added. I have enough of these links that I shouldn't have to modify all of them each time there is an insertion or deletion. I tried using the formula =HYPERLINK(A730, "Category1") or =HYPERLINK('Shawna Smith'!A730, "Category1"), and neither of these work at all. It does not jump to A730 when I click on it. What am I doing wrong, or should I be using another feature or formula? Thank you for your help. https://preview.redd.it/nb4q3dm883tg1.png?width=649&format=png&auto=webp&s=cfc0c9e7f2d7e9d620075eebf6def8e7500f09fa submitted by /u/KatMagic1977 [link] [comments]
- This Week's /r/Excel Recap for the week of April 18 - April 24, 2026Saturday, April 18 - Friday, April 24, 2026 Top 5 Posts score comments title & link 26 22 comments [solved] How to use AND and OR conditions within the same formula 18 15 comments [unsolved] Power Query Rounding Issue 13 21 comments [Waiting on OP] How to handle data from different sources when columns are in different orders? 6 5 comments [unsolved] Excel buffering while working between 2 workbooks 4 13 comments [unsolved] Vlookup on empy field with referece to line above Unsolved Posts score comments title & link 4 13 comments [unsolved] Calculating moving range in power query 4 16 comments [unsolved] how to record the exact time that I Click on a check box? and that the record will not change. 3 10 comments [unsolved] What formula(s) would I use to populate a cell depending on the info of an adjacent cell? 3 7 comments [unsolved] Automate copy from one worksheet to another using a cell reference 3 10 comments [unsolved] Generating a roll call based on recent meeting attendance Top 5 Comments score comment 637 /u/leostotch said The last time I asked copilot for Excel help, it made up an imaginary function. 208 /u/radek432 said When you perform the operation (0.9 - 0.6 - 0.3), Excel is actually dealing with tiny "remainders" created by the conversion to binary: 0.9 -> 0.89999999999999991118... 0.6 -> 0.59999999999... 165 /u/RoleNo3531 said =SUBSTITUTE(A1,"-","") 112 /u/Pestilence_XIV said To answer you question to be best of my knowledge, no. However, you could have them paste into an import sheet where formatting doesn’t matter, then you pull the data from that sheet into the one wi... 105 /u/icleanupdirtydirt said Nah. I've dabbled with copilot to do more complex things for me. It frequently seems to just pick the wrong reference cells even when explicitly told which to use. The formulas also just don't work ha... submitted by /u/subredditsummarybot [link] [comments]