On Wed, 21 Mar 2007 12:20:38 -0700, Emma Hope
wrote:

Hi All,

I have created some VBA that uses DAO to pull some data from MS Access, the
query within Access is a parameter query and uses the Excel tab (worksheet
name) as the parameter, i use "strTabName = ActiveCell.Worksheet.Name" to do
this.

My spreadsheet has 14 tabs, one for each month and a couple of others, i
have to run my code 12 times each time, saying activate the tab and run the
query, each time specifying the tab name.

I want to be able to automate this along the lines of ..... for each
worksheet in range of worksheets 2 to 13 (i don't want to name them if
possible) do the following - activate, then run my code and then repeat for
the next worksheet along.


Try this. I don't know where this code lives, but it appears to live in a
worksheet module behind an ActiveX commandbutton. Remember that you only
have to click the button once and it will process all the sheets between 2
and 13. Post back if you have more questions.

Private Sub CommandButton2_Click()

Dim Db As Database: Dim Qd As QueryDef: Dim Rs As Recordset: Dim Ws As
Object
Dim i As Integer: Dim strMonthName As String: Dim strPath As String: Dim
strTabName As String

'Set the Path to the database. This line is useful because
'if your database is in another location, you just need to change
'it here and the Path Variable will be used throughout the code.
strPath = ThisWorkbook.Path & "\Member Database.mdb"

Set Db = Workspaces(0).OpenDatabase(strPath, ReadOnly:=True)

For Each Ws In ThisWorkbook.Worksheets

If Ws.Index = 2 And Ws.Index <= 13 Then
'This set of code will activate Sheet1 and clear any existing
data.
'After clearing the data, it will select cell A1.
Ws.Range("A1").CurrentRegion.ClearContents

'Set the strings that will be passed as parameters. The strings
are
'hard coded, just for the sake of simplicity. But there are lots
'of ways to set these variables.
'NOTE: For Excel 97, change these years to 96.
strMonthName = Ws.Name

'Set the Database, and RecordSet. This Table exists in the
database.
Set Qd = Db.QueryDefs("qryFiguresMemberByWeek")

Qd.Parameters("Specify Month") = strMonthName

'Create a new Recordset from the Query based on the stored
QueryDef.
Set Rs = Qd.OpenRecordset()

'This loop will collect the field names and place them in the
first
'row starting at "A1."
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next

'The next three lines will get the data from the recordset and
copy
'it into the Worksheet (Sheet1).
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold
= True
Ws.Range("A2").CopyFromRecordset Rs

'This next code set selects the data region and auto-fits the
columns.
Ws.Range("A1").CurrentRegion.EntireColumn.AutoFit

Qd.Close
Rs.Close
End If
Next Ws

Db.Close

Set Qd = Nothing
Set Rs = Nothing
Set Db = Nothing

End Sub

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com