Automatically Update the Filename in VBA

[mushtaq.ams]

Many Many Thanks in advance.

Dear All,
I am running a series of commands in one macro, such as setting the filter criteria in File#1 (here LB – 201.xlsm) and based on the set criteria, filtering on File#2 and copying and pasting the filtered data back into File#1. Macro is running perfectly.
The problem starts when I am changing the file name of File#1 ("LB - 201" to say "LB - 202" or any other name).
I want to keep the reference "LB - 201.xlsm" as dynamic.

Here is the macro.

Please Login or Register  to view this content.

[6StringJazzer]

What file contains this VBA?

What do you mean by "dynamic"? How can VBA automatically determine what file name you have in mind? Do you want to put the filename in a cell? Can the filename be determined automatically by date?

[davsth]

I think if you are on the activeworkbook at the begining of the code creating a variable eg

Please Login or Register  to view this content.

will suffice then you reference this in the rest of the code

Dav

[mushtaq.ams]

Dear davsth

Thanks for your response.
But this didn't worked.

If you allow, then wish to send you both the excel files. Hope that will clarify my doubt in a proper way.

Thanks again.

Cheers !!!

[mushtaq.ams]

Dear String,

Thanks for your time in reviewing and posting a reply.

I think you got it correctly.
I want the vba in File#01 to determine its name automatically in the code.
Filename of File#02 remains standard (in my case).

[davsth]

what didn't work?
if for example

Please Login or Register  to view this content.

is determined at the top of the vba code. Assuming the code is in LB - 201.xlsm and not the other file

as it is what is returned by curwbname = ActiveWorkbook.Name

then you modify

Please Login or Register  to view this content.

to something like

Please Login or Register  to view this content.

and so on for the rest

what is it falling down on?

Dav

[mushtaq.ams]

Dear Dav,

Thanks for the reply.
I will try with this new code, and will update here.

Thank again.

[mushtaq.ams]

Dear Dav,

it worked fine upper half..... Thanks a ton for this.

It struck in lower half of code, where I am coming back and pasting the data.

The original was

Please Login or Register  to view this content.

I modified to

Please Login or Register  to view this content.

This is the debugged line.

[davsth]

Please Login or Register  to view this content.

Does that work

[mushtaq.ams]

Dear Dav,

Lots of respect.
Thanks a lot.


It really worked.

Many Many Thanks.

[mushtaq.ams]

Dear Dav,

Do we have anything similar for current work sheet.

What is mean is
For current workbook we define "curwbname = ActiveWorkbook.Name"

For current worksheet, can we define like this "curwksheet = ActiveWorksheet.Name"

[davsth]

the name can be anything i just made up curwbname as it made vague sense, but I could have called it mushtaq or dav or..... its just a name
amazingly the current worksheet is ActiveSheet.Name, you were close!

so something like curwsname= ActiveSheet.Name

[mushtaq.ams]

Dear Dav,

Thanks a lot and
again Many Many Thanks.

Your guidance was really helpful.

Wish you loads of success.
Cheers !!!

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.

[mushtaq.ams]

Dear 6String,

Thanks for the info. I will do that.

[mushtaq.ams]

Dear Dav,

Just a quick question.
If i define a name of work book in a cell (say in cell H2 as "Beam Design Summary.xlsm" or anything, can we call this reference in VBA )
I hope i am clear in my question.

[davsth]

Sorry the internet has been down at work, with all the IT issues since friday, only just back up

yes mushtaqname=range("H2").value
or mushtaqname=sheets("Mushtaqsheet").("H2").value

obviously you can change mushtaqname an mushtaqsheet to whatever you see fit

[mushtaq.ams]

Dear Dav,

Thanks for the reply, and I wish and hope the services at your end and around the world restores quickly.

Thanks for help with above syntax.
But unfortunately it didnt worked.

I posted a new thread for the same and below is the link for that .
https://www.excelforum.com/excel-pro...ml#post4657487