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

Morshed_Dhaka

New Member
Joined
Dec 16, 2016
Messages
42
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
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.
 
Upvote 0
Try this:
Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,708
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top