Copy-Paste sheet from other Files with different variables

[Timal03]

Hello to all,

I would like to build a macro in order to open files according to different criteria (I will develop it) and then copy and paste a sheet from this file to my master file.
You can see the macro I recorded below which will be a base to explain my problem.

The main problem is to play with the variables, I am just discovering VBA language and confess I am a bit lost...

The first variable would be the “YearFile” corresponding here to : 2012_06
I build this variable under Excel with the following formula: (YearHotel_H1 corresponding to a name of a cell where I choose the year I need, and G6 corresponding to the current year. Then I say that if I choose a year before the current one I have to go in the file with the name YYYY_12, and if it is the current year I choose then the file will follow the format YYYY_MM (MM
=IF(YearHotel_H1<G6,YearHotel_H1&"_"&"12",IF(YearHotel_H1=G6,YearHotel_H1&"_"&IF(M3<10,"0"&MONTH(1&G3),MONTH(1&G3))))

The second variable would be the “RegionFile” here is: “Asia”
The formula under excel is: (Region_H1 corresponding to the name of my cell)
=IF(Region_H1="Asia","Asia","Pacific")

Then the 3rd and last variable would be the “HotelFile”: Here is H1555 – 2012_06
The formula on excel would be: (HotelCode_H1 is also a name of cell)
=HotelCode_H1 & " " & "-" & YearFile & “.xls”

How can I write it in VBA ?

Please Login or Register  to view this content.

Then to sum up, I would like to have a macro which automatically copy and paste the sheet according to the year and the hotel I chose previously on Excel (thanks to tab list)

Thank you very much for your help !

Best

[Cutter]

@ Timal03

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.