Using wildcards in workbook calls
 

On Apr 28, 3:51*pm, Bishop wrote:
Why can't I get the following code to work? *I want this macro to find an
open workbook called "ExportedData---" (where the dashes could be be
anything) and perform the following actions. *That's why I'm using the *
wildcard. *For example, one instance of the workbook might be named
"ExportedData[2]" *the next instance might be called "ExportedData[1]". *As
you can see from my comments I've tried messing around with a few different
methods but just can't make it work. *In this current setup I'm getting a
"Compile Error: *Argument not Optional" and it highlights the word Workbooks
in the With statement.

Sub CatalystToTally()

'Dim wb As Range
'Dim ws As Range
'Set wb = Workbooks
'Set ws = Worksheets

* * With Workbooks Like "ExportedData*"
* * * * '.Rows(1).Delete Shift:=xlUp
* * * * .Rows("1:1").Delete Shift:=xlUp
* * * * .Columns(D).ColumnWidth = 13
* * * * .Columns(D).NumberFormat = "0"
* * * * .Cells.Select
* * * * .Selection.Copy
* * * * .ThisWorkbook.Worksheets("Catalyst Dump").Paste
* * End With
End Sub

Bishop,

The program is throwing an error on the "Workbooks" line because, in
your specific example, when you qualify the workbooks class, you then
need to tell VBA what method you want to perform or property
information you want to read/write. In your example, you are looking
to compare the workbook NAME with the "ExportedData*" wild card
comparison. Below is an example that should give you enough syntax to
play around with. Also, be sure to evaluate your code, line-by-line
(using F8 repeatedly or Debug | Step Into) to see how your programs
are behaving.

Best,

Matthew Herbert

Sub CatalystToTally()

Dim Wkb As Workbook
Dim Wks As Worksheet

'loop through the workbooks collection
For Each Wkb In Workbooks

'test if Wkb's name is Like "ExportedData*"
If Wkb.Name Like "ExportedData*" Then

'create a worksheet object to reference the
' appropriate worksheet in Wkb
Set Wks = Wkb.ActiveSheet

'manipulate Wks
With Wks
.Rows(1).Delete
.Columns("D").ColumnWidth = 13
.Columns("D").NumberFormat = "0"

'copy and paste Wks into the specified destination (i.e.
' the destination is the workbook in which the VBA code
' resides)
.Cells.Copy ThisWorkbook.Worksheets("Catalyst Dump").Range
("A1")
End With
End If
Next

End Sub