How can I create a variable that will hold worksheets objects/worksheet names?

[keithaul]

I want to preform certain code on designated worksheets in my workbook. I only want to do this code for some of the worksheets, but not all of them.

What I would like to do is the following:

For Each ws in ActiveWorkBook.Worksheets
'do some code
Next ws

Since I only want to run the code within the for loop for certain worksheets, I don't know how to replace 'ActiveWorkBook.Worksheets' with another variable that will hold the worksheet objects/names.

I've tried this:
Dim arrayone as variant
arrayone = array("worksheetname1", "worksheetname4")

I then used the arrayone variable in the for loop but this doesn't seem to work. I get an object required error.

I may not be understanding when I should reference an object or the sheetnames.

Does anyone know how I can reference some of the worksheets, but not all of them, in VBA code?

 

[mikerickson]

You could do

Dim oneSheet As Worksheet
Dim mySheets as Sheets

Set mySheets = ThisWorkbook.Sheets(Array("worksheetname1", "worksheetname4"))

For each oneSheet in mySheets
    ' some code
Next oneSheet

or

Dim ws As Worksheet

For Each ws in Sheets(Array("worksheetname1", "worksheetname4"))
    ' some code
Next ws

In either case, you are looping through a Sheets object of your own description, rather than looping through the Worksheets collection object as in

Dim ws as Worksheet

For Each ws in ThisWorkbook.Worksheets
    If ws.Name = "workhsheet1" or ws.Name = "worksheet4" Then
        ' some code
    End If
Next ws

 

[Michael M]

Maybe this...

Sub MM1()
Dim Sh As Worksheet
For Each Sh In Sheets(Array("worksheetname1", "worksheetname2"))

'your code here

Next Sh
End Sub

 

[keithaul]

Update on this:

Below is some code that is suppose to select certain worksheets in my workbook and then make a backup copy of the data on each of those sheets to a text file. Right now every is working. However, since my worksheets have navigation shapes on the left side of the sheet and each data range actually starts in cell F3, the ws.copy code actually copies the empty cells where the shapes are.

I don't want this to happen. So in each sheet I have a table and I've named those tables in each sheet. There is only one table on each sheet.

The end result I want is to copy just the table data, not the empty cells to the left of the data where the shapes are located.

I've tried using ListObjects but I can't figure out how to do it with the ListObjects collection

Since I have an array set up in the code with the actual sheet names, How do I reference the data range for each table on each sheet named in the array and then select that range and copy it using VBA?

Sub BackUpData()
Dim wbkCur As Workbook
Dim wbkNew As Workbook
Dim ws As Worksheet
Dim arrayone As Variant
Dim ThisFN As String

Set wbkCur = ActiveWorkbook
arrayone = Array("Sold Items", "Amazon")

'Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
' Remember to set the ScreenUpdating property back to True when your macro ends.
Application.ScreenUpdating = False

'suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.
Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets(arrayone)


ws.Copy
Set wbkNew = ActiveWorkbook

'create a File name for the current workbook(the current path of the workbook) and path and append the current date and time of day to the file name
ThisFN = wbkCur.Path & "\" & ws.Name & "_" & Format(CStr(Now), "mm_dd_yyyy_hh_mm_AM/PM") & ".txt"

'save the new workbook with a file type of Text and dont create a backup file
wbkNew.SaveAs Filename:=ThisFN, FileFormat:=xlText, CreateBackup:=False

'close the new workbook and don't save the changes
wbkNew.Close SaveChanges:=False
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Norie]

If there is only 1 table on each worksheet try this.

Sub BackUpData()
Dim wbkCur As Workbook
Dim wbkNew As Workbook
Dim ws As Worksheet
Dim arrayone As Variant
Dim ThisFN As String

    Set wbkCur = ActiveWorkbook
    arrayone = Array("Sold Items", "Amazon")

    'Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
    ' Remember to set the ScreenUpdating property back to True when your macro ends.
    Application.ScreenUpdating = False

    'suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.
    Application.DisplayAlerts = False

    For Each ws In ActiveWorkbook.Worksheets(arrayone)

        Set wbkNew = Workbooks.Add(xlWBATWorksheet)

        ws.ListObjects(1).Range.Copy wbkNew.Range("A1")

    'create a File name for the current workbook(the current path of the workbook) and path and append the current date and time of day to the file name
        ThisFN = wbkCur.Path & "\" & ws.Name & "_" & Format(CStr(Now), "mm_dd_yyyy_hh_mm_AM/PM") & ".txt"

    'save the new workbook with a file type of Text and dont create a backup file
        wbkNew.SaveAs Filename:=ThisFN, FileFormat:=xlText, CreateBackup:=False

    'close the new workbook and don't save the changes
        wbkNew.Close SaveChanges:=False
    Next ws

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

 

[keithaul]

thanks for your input.

However I'm getting this error: Object doesn't support this property or method

This error happens on the following code:
ws.ListObjects(1).Range.Copy wbkNew.Range("A1")

Any thoughts?

 

[Norie]

Oops, forgot wbkNew is a workbook.

        ws.ListObjects(1).Range.Copy wbkNew.Sheets(1).Range("A1")

 

[keithaul]

OK. Thank you that worked. Now I would like clarification on why this works. I'll put comments in for each line of code I have a question on.


'The below line is just creating a new workbook that has one sheet in it to the variable of type Workbook. Is that correct?
Set wbkNew = Workbooks.Add(xlWBATWorksheet)


'The below code is where I don't have a full understanding on
ws.ListObjects(1).Range.Copy wbkNew.Sheets(1).Range("A1")


Why don't I have to set up a variable like : Dim lstObject as ListObject?

In this 'For Each ws In ActiveWorkbook.Worksheets(arrayone)' code:

1. The ws.ListObjects is a collection of table names in each worksheet. Is that correct? Or does it contain other info?

2. My array is set up as: Array("Sold Items", "Amazon"). The 2 values in the array are the name of my sheets I want to back up. What does the (1) mean in ws.ListObjects(1)? Since were in the 'For each' loop for the first time, the current sheet is 'Sold Items' which is assigned to 'ws'. Does ws.ListObject(1) refer to table 1 in that sheet since there is only 1 table on it? And we use ws.ListObjects(1) because I know there is 1 table in there and for the future there will never be another table in that sheet. Is what I'm saying here correct?

3. The 'Range.Copy' in ws.ListObjects(1).Range.Copy just takes the know range of the table, which includes the headers and data, correct?

4. As for the 'wbkNew.Sheets(1).Range("A1")': I know this performs the paste, correct? This pastes the range of the table to the new workbook on sheet 1 starting in cell A1

5. why don't you use something like this to paste the data: ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1")

6. As for this entire linke: ws.ListObjects(1).Range.Copy wbkNew.Sheets(1).Range("A1") - I'm assuming this must be some shortcut method to copy and paste, correct? Is there somewhere online where I can learn vba code to put multiple actions on one line?

Keith

 

[Norie]

Keith

This creates a new workbook with only one sheet.

Set wbkNew = Workbooks.Add(xlWBATWorksheet)

You could use a variable here but since all we are doing is copying the only table on the sheet it's not really worth it.

ws.ListObjects(1).Range.Copy wbkNew.Sheets(1).Range("A1")

If we were going to manipulate the table, eg formatting it, adding/deleting data, a variable might come in handy.

1 Yes, ws.ListObjects is a collection of the list objects (tables) on the sheet ws.

2 ws.ListObjects(1) refers to the first, and in this case only, list object (table) on the sheet ws.

If there were more tables and you wanted to copy a specific table you could replace the 1 with the table name.

3 Yes. Try adding this to the code and comparing the output in the Immediate Window (CTRL+G) to the range the table covers.

Debug.Print ws.ListObjects(1).Range.Address

4 Yes.

5 You could use something like that I suppose, not sure if there's any advantage. Mind you if you had formulas in the table and only wanted values you could use PasteSpecial.

6 Not really a shortcut, that's just the way Copy works for a range