Formulas being modified by VBA code unintentionally

[UncleBajubjubs]

Hello, I have a workbook used to determine the cost of constructing machinery. There is one worksheet which is a massive list of parts (bolts, screws, etc.) and their costs. The user will select what parts they need on other sheets, and their are cells to multiply the cost of what they need by the number selected.

As the prices of things change, the parts sheet is updated frequently, so there is a VBA button to update the parts sheet with a file saved on a network folder. This is meant to erase the parts sheet and replace it with the new data. Unfortunately, It shifts the data 13 cells to the right, and moves the cells in formulas 13 spaces to the right.

For instance, if I currently have a formula on another worksheet look up Parts!$A1, after running the formula, it will now look up Parts!$N1. So it continues to look up the old data.

The code currently is:

<code>
Dim bUsername As String
Dim PartsPath As String


Dim xConnect As Object '' this is code to delete all existing data connections
For Each xConnect In ActiveWorkbook.Connections
If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete
Next xConnect




bUsername = Environ$("username")


Select Case bUsername
Case "exampleuser"
PartsPath = "FILepath.html"

Worksheets("Parts").Activate 'activates parts, goes to the URL specified by PartsPath variable, and pulls in the tables
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & PartsPath, Destination:= _
Range("$A$1"))
.Name = "parts_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

MsgBox "Parts connection successful for " & bUsername & ". Parts path is " & PartsPath
Worksheets("MAIN WORKSHEET").Activate

</code>

I'm at a loss here, any ideas?
Thank you.

 

[mse330]

UncleBajubjubs, try the below in a sample file - Take backup of your original file

Replace this line of code

.RefreshStyle = xlInsertDeleteCells

with this

.RefreshStyle = xlOverwriteCells