Excel Macro help

[Thomas. PLease.]

Hi there,

Part of my job entails creating two spreadsheets, based on data from a report spreadsheet that is generated daily. This spreadsheet has a new name everyday, which coincides with the date.

I need to copy data from this report and copy it into a template. I then use my own macro to copy the relevant data from the template into what is known as the retail spreadsheet.

I can write a macro to import the data from the report to the template, but as the name changes everyday it will only work for that day.

So, how is it possible to import this data using a new spreadsheet everyday?

Thanks. Tom.

 

[Doughnut007]

You could use an imput box which will ask you for the name of the spreadsheet to import the data from.

Or use a cell in the workbook in the same way.
Or if the spread sheets just has the same name but with the date in it then you should easily be able to put that into the macro so it would use that days date when it is run.

 

[Doughnut007]

Think you can also use the standard windows file browser but I cant remember the code of my head.

Try google there are loads of good excel forums

 

[Thomas. PLease.]

Thanks for the answer, I think the input box may be the best solution. The filename is a coded date along with a branch code, so its varies quite significantly daily.

Thanks again.

 

[A[L]C]

Sub RetrieveFileName()

Dim sFileName As String



'Show the open dialog and pass the selected _

file name to the String variable "sFileName"



sFileName = Application.GetOpenFilename

'They have cancelled.

If sFileName = "False" Then Exit Sub

MsgBox sFileName

 

[Thomas. PLease.]

Hi there, thanks very much for that.

I'm pretty much clueless about macro's but I managed to do this.

I now want to copy data from the report which I now have the name of.

This is what I have so far.

A[L]C's code finishing

....
MsgBox sFileName

I have then written...

Workbooks.Open sFileName

which opens the spreadsheet.

What do I need to write to refer to this spreadsheet when I want to copy and paste.

I have:

Range ("B2:1000").Select
Selection.Copy
Windows ("Copy of Declaration Exceptions Template (Read Only).xls").Activate
Range ("A2").Select
ActiveSheet.Paste

But where I'm stuck is what do I use to refer back to the sFilename spreadsheet, as whatever I seem to type it doesn't seem to work?

Thanks for the help.

 

[Thomas. PLease.]

Can anyone help with this, it would be massively appreciated.

 

[dazzerd]

If the spreadhsheet you want to refer to is already open in the same instance of excel then use something like:

windows(sfilename).activate

This will activate any window that has been defined as sfilename.

 

[Thomas. PLease.]

brilliant thanks very much.