I have data that is generated every day and is saved to a new
spreadsheet with the name CallListYYYYMMDD.xls. When this data is
saved, I copy all data to another spreadsheet with the name ALTERNATE
LOAD DATE FORM.xls. Once here I have macros to format the sheet like
I need for future use. I have other spreadsheets with macros that
copy from one workbook to another, but they have the same name
everytime data is copied.

My problem that I need help with is the fact that the name of the
original data sheet is constantly changing to match the day that it
was generated.

Any help would be great.

Thanks
Allen

Maybe you could have your macro open the file you specify.


Dim CLWkbk as workbook
dim clwkbkname as variant

clwkbkname = application.getopenfilename(filefilter:="Excel Files, *.xls")
if clwkbkname = false then
exit sub 'user hit cancel
end if
set clwkbk = workbooks.open(filename:=clwkbkname)

Then use clwkbk in your code
clwkbk.worksheets(1).range("a1").value = date
clwkbk.worksheets(1).range("a1:x99").copy _
destination:=thisworkbook.worksheets("somesheetnamehere").range("x99")



jonallen51 wrote:
>
> I have data that is generated every day and is saved to a new
> spreadsheet with the name CallListYYYYMMDD.xls. When this data is
> saved, I copy all data to another spreadsheet with the name ALTERNATE
> LOAD DATE FORM.xls. Once here I have macros to format the sheet like
> I need for future use. I have other spreadsheets with macros that
> copy from one workbook to another, but they have the same name
> everytime data is copied.
>
> My problem that I need help with is the fact that the name of the
> original data sheet is constantly changing to match the day that it
> was generated.
>
> Any help would be great.
>
> Thanks
> Allen

--

Dave Peterson