VBA-Macro Copy & Value Paste all non hidden Sheets data

[Morshed_Dhaka]

Hello Everyone,

I am looking for a VBA-Macro help . I already complete the macro by using macro recorder. But every time, I need to change that code when the sheet name was changed. Please, anyone kindly help. Here is description of my problem :

1. I have a excel macro enabled file which has 34 sheets
2. 1st Sheet named as "START UP" which is fixed, not changing time to time
3. 2nd sheet named as "NATIONAL" which is fixed, not changing time to time
4. 3rd sheet named as "REGIONAL" which is fixed, not changing time to time
5. 4th to 34th sheet named as "01-01-2018", "02-01-2018","03-01-2018" to "31-01-2018" . It is basically 31 days of the Month January 2018. If the month is March 2018 then the sheet name will changes to "01-03-2018" to "31-03-2018"

I have code by using macro recorder that shows :

a) I am hiding "start up", "national" & "regional" sheets
b) Selecting all visible sheets which is "01-01-2018" to "31-01-2018"
c) Copy > Value Paste all those sheets selected ranges data
d) after copy > value paste , unselect all the sheets

the problem is by macro recorder, they recognizes all the data wise sheets name in macro. But I am looking for a macro that will run the copy>value paste operation in all the visible sheets. If I change the sheet name, for example "01-01-2018" to "01-05-2018" the code will work because it is not considering the sheet name.

Please, kindly help me by provide your suggestion.

Thanks.

 

[My Aswer Is This]

So with all visible sheets you want to:

Copy > Value Paste all those sheets selected ranges data

What does this mean?
Copy > Value Paste all those sheets selected ranges data​

Copy what values and paste where?

 

[Morshed_Dhaka]

Copy & Value Paste Same Data in Same Range as because originally those are formulas which pulling data from another file. When I want share the file, I have value paste those data so that it will not create any linked file.

 

[My Aswer Is This]

Try this:

Sub Sheet_Values()
'Modified 2-24-18 6:15 AM EST
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To Sheets.Count
    If Sheets(i).Visible = True Then Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
    Next
Application.ScreenUpdating = True
End Sub

 

[Morshed_Dhaka]

many thanks. it worked nicely.

 

[My Aswer Is This]

many thanks. it worked nicely.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Morshed_Dhaka]

Many Thanks again.

Why not? I am trying my level best to solve the critical problems by myself. If I was unable then I was looking for elite people's help by giving post to Mr.Excel. I already provide another post for another help "VBA-Macro for Converting Multiple Excel .XLSM file into PDF File" . Will be grateful if you able to help. Thanks.