macro help

[Donna]

I have a macro for a report and I want to add a worksheet to the macro. My
question is I already have it typed in an excel worksheet. Is there a way to
copy it in the macro without having to re type it to add it to my existing
macro?
Donna

 

[Dave Peterson]

You want to add a worksheet from an existing workbook?

If that existing workbook contains a single sheet:

Dim Sh as object
set sh = sheets.add(type:="c:\pathtothatfile.xls")

Actually, this will add all the sheets in that workbook.

If you wanted to pick out a single sheet, you could use something like:

dim wkbk as workbook
Dim wks as worksheet
dim wkbkName as string
dim wksName as string
dim ActWkbk as workbook

set actwkbk = activeworkbook 'the recipient workbook

wkbkname = "C:\pathtothatfile.xls"
wksname = "somesheetnamehere"

set wkbk = workbooks.open(filename:=wkbkname, readonly:=true)
set wks = wkbk.worksheets(wksname)

wks.copy _
after:=actwkbk.sheets(actwkbk.sheets.count)

wkbk.close savechanges:=false

==========

Untested, uncompiled. Watch for typos.