is it possible in VBA

[LearnMeExcel]

hi

is it poosible in VBA, to make A lot of Copy Form My File With Certian Name, for example

my wb have around 15 ws
and this wb for 22 Products, in master sheet when i select Product all analysis update direct for this product, what i want to do

1. change all sheets to Value (copy - paste special value)
2. important point- to save the file with this product , then REOPEN master file, then do same steps for second Product, until Produc 22

any help will be appreciated

thank you in advanced

 

[dermie_72]

If you record your actions with the macro records on one sheet,

Then you can do a loop in the workbook
for each sheet in sheets
your recording
next
This will do the same action in each sheet

 

[LearnMeExcel]

If you record your actions with the macro records on one sheet,

Then you can do a loop in the workbook
for each sheet in sheets
your recording
next
This will do the same action in each sheet

this is not the issue
when you make a loop through Product name, master file will close and saved with prodcut name
HOW I CAN MAKE Excel reopen MAser file autmaticlly and then do same steps for Product 2 ......etc....


i hope it is clear

 

[LearnMeExcel]

any idea please friends

 

[dermie_72]

You could add the workbooks.open function to the code (If you're not sure of the exact text, record opening the master file. Stop recording once open, then review the code.
Otherwise, look to where the master file is being closed and remove that section.

You could also use a code to see if the file is already open, and if not, open it.

dim alreadyopen as Boolean
dim wb as workbook

For Each wb In Workbooks
    If wb.Name = "Master.xlsx" Then
        alreadyopen = True
        Set Master= wb
        Exit For
    End If
Next wb
If alreadyopen = False Then
    Workbooks.Open Filename:="C:\Master.xlsx", ReadOnly:=True
    Set Master= ActiveWorkbook
    Else
End If