macro on a seperate workbook

[Jim]

Is there a way that I can have a macro in a workbook titled let's say
cleardata.xls and have another workbook called input.xls that would clear
the data in certain rows and columns in the input workbook?

Thank you,

Jim

 

[Dave Peterson]

Inside cleardata.xls

workbooks("input.xls").worksheets("sheet1").range("a1:b9").clearcontents

Assumes that input.xls is already open.

or maybe:

workbooks("input.xls").worksheets("sheet1") _
.range("a:c,f:g,1:3,8:19").clearcontents

 

[Jim]

Dave,

Thanks for your help, I was able to get this to work with your help. One
more question though, if the name of my workbook changes everyday is there
a wildcard that I can use in the macro without having to go in and edit it
every day.

Here is the macro I have below, where the date is 08 20 2003 that will
change every day, what wildcard can I use or can I use a wildcard?

Windows("CMB ACD 08 20 2003.xls").Activate
ActiveWindow.SmallScroll Down:=51
Range("A2:AE53").Select
Selection.ClearContents
Windows("cleardata.xls").Activate

Thanks,

Jim

 

[Dave Peterson]

How do you open the workbook? Is it in code?

If yes, you could assign the workbook to a workbook variable and just use this.
You won't need the name in your code at all.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim myWkbk As Workbook

myFileName = Application.GetOpenFilename("Excel files, *.xls")
If myFileName = False Then
'user hit cancel
Exit Sub '??
End If

Set myWkbk = Workbooks.Open(Filename:=myFileName)

'Now you can refer to the variable mywkbk instead of the actual name.
with myWkbk.worksheets(1) 'do you know the name of the worksheet?
.range("a2:ae53").clearcontents
'do more stuff???
end with

End Sub

I tend to stay away from the Windows collection.