Combining Data From Variable Number Of Tables

I have a requirment to take data from a large set of tables where the
total number of these tables may change on a regular baisis and
output into another table. All the tables willl have the same
columns. Frequency is being debated but it maybe as much as once per
hour.

Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.

2) then I need the following
for each of the tables found above, I need the outfrom from each of
those tables to be inputted into another table. basically, I would
want the following output from each of the tables found in step 1

select machineid,name from _cpy_offermanager_678

3) In the end I would have something like dbo.ALLCPY with records
combined from all other _CPY tables

Ron Sorrell