Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 9th, 2007, 01:52 PM
Registered User
 
Join Date: Feb 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Removing a specific sheet from a loop in Excel

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!

 
Old February 9th, 2007, 01:54 PM
Registered User
 
Join Date: Feb 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
How to position to a specific sheet before saving pkipe Excel VBA 1 December 17th, 2007 10:36 PM
Copy specific data from one sheet to another yogeshyl Excel VBA 2 May 11th, 2007 09:14 AM
How To Export a specific Excel Sheet as a csv file mrjits Excel VBA 5 August 1st, 2006 03:04 PM
write multi-sheet Excel w/o Excel.Application manmoth Classic ASP Components 2 November 22nd, 2005 10:56 AM
Removing external links in Excel tcarnahan Excel VBA 9 February 22nd, 2004 07:21 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.