macro on a seperate workbook

  • Thread starter Thread starter Jim
  • Start date Start date
J

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
 
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
 
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
 
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.
 
Back
Top