Hi Everyone

I have got an Excel program that retrieves colunms of data from a Database using MS Query on the first sheet of the workbook. I then have some code that abstracts the data from from the first sheet into each seperate sheet depending on the number value in the Family Groups column. This is all done using a loop.

This all worked fine until I tried to insert another sheet which contains a couple of columns of static values which I dont wish to be affected by the VBA loop. when I now run the code it overwrites the contents of this sheet.

May question is how can I put some code into my loop to prevent it from updating this sheet in the work book?

Any help will be greatly appreciated!

Hi here is the code below

I have highlighted in red below where I believe the new code should be inserted(possibly a If statement)

Private Sub Extrapolate()

On Error GoTo EndLine

CataloguePageNo = "A"
PartNumber = "B"
TotalStock = "C"
FrozenIndicator = "D"
Description001 = "E"


ProductCode = "A"
Description = "B"
PrintDate = "C"
Quantity = "D"

Dim Sheetx As Object

For Each Sheetx In Worksheets

If Sheetx.Name = ActiveSheet.Name Then GoTo NextLine

' This is where I believe the the new line of code should go


Sheetx.Range("A:G").ClearContents

' Adds column headings to each sheet in workbook

Sheetx.Range("A1").Value = Range("G2").Value
Sheetx.Range("B1").Value = Range("M2").Value
Sheetx.Range("C1").Value = Range("H2").Value
Sheetx.Range("D1").Value = Range("I2").Value
Sheetx.Range("E1").Value = Range("J2").Value
Sheetx.Range("F1").Value = Range("K2").Value
Sheetx.Range("G1").Value = Range("L2").Value
EntryRow = 2

LastRow = Range(PartNumber & 1).End(xlDown).Row
If LastRow = 65536 Then LastRow = 1

' Runs a loop that checks which CataloguePageNo(Family Group) is on that record of data on the first Live Data sheet(MS Query data)
' and selects the relevant sheet with that CataloguePageNo(Family Group) name

For a = 2 To LastRow
Select Case Val(Range(CataloguePageNo & a).Value)
Case Val(Right(Sheetx.Name, 3))

' If the PartNumber has a print date on the end then split the date from the rest of the PartNumber and assign to new varibles


If Right(Range(PartNumber & a).Value, 9) Like "(####/##)" Then

ProdCode = Mid(Range(PartNumber & a).Value, 4, Len(Range(PartNumber & a).Value) - 12)
PDate = Mid(Right(Range(PartNumber & a).Value, 9), 2, 7)

Else

ProdCode = Mid(Range(PartNumber & a).Value, 4)
PDate = Empty

End If

' add values from MS Query data columns to columns on each on stock check sheets

Sheetx.Range(ProductCode & EntryRow).Value = ProdCode
Sheetx.Range(PrintDate & EntryRow).Value = PDate
Sheetx.Range(Quantity & EntryRow).Value = Range(TotalStock & a).Value
Sheetx.Range(Description & EntryRow).Value = Range(Description001 & a).Value

EntryRow = EntryRow + 1

Case Else
End Select

Next a
NextLine:
Next Sheetx

MsgBox "Complete"

Exit Sub