Wilcard for Workbooks???

A

Awrex

I have a weekly report which I generate a csv for the data and populate in a
standard xls workbook. The xls workbook only changes it's name based on date
it's generated. The csv normally only has deployments.csv & Weekly
Deployments test.xls usually ends with a date format not "test"(I changed the
code below during testing).

Aside from generating more code to make the xls workbook fromscratch or
adding a worksheet and moving the csv data over, is there a wildcard I can
use for the active workbook??

Here is some of the code:
--------------------------------------------------------------------------
Windows("deployment-1.csv").Activate
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(103,R[1]C[-2]:R[1985]C[-2])"
ActiveCell.Offset(0, -4).Columns("A:C").EntireColumn.Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=*10.53**", Operator:=xlOr, _
Criteria2:="=*pswe**"
ActiveCell.Offset(0, 4).Range("A1").Select
Selection.Copy
Windows("Weekly Deployments test.xls").Activate
ActiveCell.Offset(4, 7).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("deployment-1.csv").Activate
Selection.AutoFilter Field:=3, Criteria1:="=*10.63**", Operator:=xlOr
Application.CutCopyMode = False
Selection.Copy
Windows("Weekly Deployments test.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
-----------------------------------------------------------------------------
 
D

Dave Peterson

If it's the active workbook when you start, you can save your place:

Dim ActWkbk as workbook
set actwkbk = activeworkbook
'do lots of stuff
actwkbk.activate 'instead of Windows("Weekly Deployments test.xls").Activate



I have a weekly report which I generate a csv for the data and populate in a
standard xls workbook. The xls workbook only changes it's name based on date
it's generated. The csv normally only has deployments.csv & Weekly
Deployments test.xls usually ends with a date format not "test"(I changed the
code below during testing).

Aside from generating more code to make the xls workbook fromscratch or
adding a worksheet and moving the csv data over, is there a wildcard I can
use for the active workbook??

Here is some of the code:
--------------------------------------------------------------------------
Windows("deployment-1.csv").Activate
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(103,R[1]C[-2]:R[1985]C[-2])"
ActiveCell.Offset(0, -4).Columns("A:C").EntireColumn.Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=*10.53**", Operator:=xlOr, _
Criteria2:="=*pswe**"
ActiveCell.Offset(0, 4).Range("A1").Select
Selection.Copy
Windows("Weekly Deployments test.xls").Activate
ActiveCell.Offset(4, 7).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("deployment-1.csv").Activate
Selection.AutoFilter Field:=3, Criteria1:="=*10.63**", Operator:=xlOr
Application.CutCopyMode = False
Selection.Copy
Windows("Weekly Deployments test.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
-----------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

works in 2007 but not in 2010 0
Help me4 2
stopping a macro 0
Condense code 2
Help with code please 4
Something in this is causing a crash 2
Help merging two VBA codes 2
how to use Solver in Excel VBA 1

Top