array

[SteveDB1]

hi all.
I recorded a macro to copy two worksheets with specific names from an open
workbook to a workbook with a specific name.

I then looked at the code it made, and saw that I got the following.

Sheets(Array("SUM", "APN")).Select

Sheets(Array("SUM", "APN")).Copy
Before:=Workbooks_("TROABook.xlsx").sheets(65)

The first thing is that I wanted it to copy the worksheets to the end of the
book.
I've tried setting the code to copy After, and changing sheets(65) to
worksheets.count, as well as sheets.count. It rejects both, stating that
"copy method of sheets class failed.

My second issue is that while the second element in my array is always
constant, the first is not.
I would like to set up a variable name for the first and thought that an
if/elseif function would help that.
however, I'm not clear on how I should accomplish that.

 

[Dave Peterson]

Maybe...

Sheets(Array("SUM", "APN")).Copy _
after:=Workbooks("TROABook.xlsx") _
.sheets(workbooks("TROABook.xlsx").sheets.count)

You want to make sure that you're counting the sheets/worksheets in the right
workbook.

 

[SteveDB1]

Hi Dave,
as always... you're good! Thank you so very much.
now for the second part.

My second issue is that while the second element in my array is always
constant, the first is not. I would like to set up a variable name for the
first and thought that an if/elseif function would help that. however, I'm
not clear on how I should accomplish that.

 

[Dave Peterson]

How do you know what sheet you want to include?

dim myFirstName as string
dim wks as worksheet

myfirstname = ""
for each wks in activeworkbook.worksheets
'some sort of test to figure out that first name
if lcase(wks.range("a1").text) = lcase("ok") then
myfirstname = wks.name
exit for 'stop looking
end if
next wks

if myfirstname = "" then
msgbox "the first worksheet wasn't found!
else
Sheets(Array(myfirstname, "APN")).Copy _
after:=Workbooks("TROABook.xlsx") _
.sheets(workbooks("TROABook.xlsx").sheets.count)
end if

(Untested. Uncompiled. Watch for typos.)