Referencing the File Name dynamically within the VBA code

[tarquinkrikery]

Hi,

I'm fairly new to this, but I've made a macro which produces email bonus statements for people at work.

It copies data into a template, emails it to the relevant person, saves a copy and then loops to the next person etc.

However when I 'flip back' from the saved/closed workbook to the main workbook I reference it's name. I was hoping to reference a dynamic filename so if I called the file "Bonus -Week 1", and next week "Bonus -Week 2" etc, I wouldn't have to change this in the VBA. Can anyone point me in the correct direction?

Cheers

Here's the bottom part of my code - hopefully it's worked and highlighted the line in question! Cheers

Please Login or Register  to view this content.

[k64]

I would just use:

Please Login or Register  to view this content.

Note that activating the workbook and selecting the sheet is usually unnecessary. It mainly has a visual effect. You can just use a with block to perform actions on that workbook/sheet without having to activate it.

[tarquinkrikery]

Thanks for your help, this worked a charm. Sorry I thought I'd posted my thanks yesterday!

Learned something new about with blocks so appreciate the time taken to help me out!

[k64]

You're welcome. Glad I could help!