Needs Help With VBA Automation of Keys

Greetings experts,

I am trying to automate some tasks on several tabs. Attached here is an example file with a replica of the tabs that I am working with(The sheets are left empty intentionally).
example.xlsm

For the first tab, the "Allocation" tab, the steps I need to carry out are
1) Select all
2) Copy and Paste As "Values" onto the same sheet

From the second to eight tab which are "By Ctrn-EIN", "By Ctrn-EMSB", "By Ctrn-ETH", "By Ctrn-EPC", "ESD Trf Qty", "EVNL Trf Qty" and "By Ctry-IDC", the steps I need to carry out are:
1) Alt + F9 (Refresh The Sheet)
2) Select all
3) Copy and Paste As "Values" onto the same sheet

For the last tab "Subset List", it should be left untouched.


Any help is much appreciated :)
LVL 4
An Average Forum Participant Just For FunHardware Tester and DebuggerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
I suggest you break your tasks into smaller tasks, and then use the Record Macro option to generate the VBA codes, and modify from there

hope this make sense
An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
@Ryan Chong Hi, thank you for your reply :)

I have recorded and edited the Macros as shown below. The AllocationTab script is supposedly for the first sheet and the OtherTabs script is for 2nd to 8th sheet. It would be great if you can take a look to see if the codes are universally correct :)

Sub AllocationTab()

    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Open in new window


Sub OtherTabs()
    
    ActiveSheet.Calculate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub

Open in new window


I need help making these scripts into functions and making the final subroutine.
The Allocation Tab script should run for only one tab named "Allocation" and the OtherTabs script should run from the 2nd to 8th sheet which are named "By Ctrn-EIN", "By Ctrn-EMSB", "By Ctrn-ETH", "By Ctrn-EPC", "ESD Trf Qty", "EVNL Trf Qty" and "By Ctry-IDC".
Ryan ChongSoftware Team LeadCommented:
you could do it like this way...

Dim ws As Worksheet

Sub Button1_Click()
    Application.ScreenUpdating = False
    Call CopyPasteValuesInTab("Allocation")
    Call CopyPasteValuesInTab("By Ctry-EIN")
    Call CopyPasteValuesInTab("By Ctry-EMSB")
    Call CopyPasteValuesInTab("By Ctry-ETH")
    Call CopyPasteValuesInTab("By Ctry-EPC")
    Call CopyPasteValuesInTab("ESD Trf Qty")
    '...
    Application.ScreenUpdating = True
    
    MsgBox "Done!"
End Sub

Sub CopyPasteValuesInTab(ByVal SheetName As String)
    Set ws = Sheets(SheetName)
    ws.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
End Sub

Open in new window


you can modify it to add in the validation and error handling, etc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
@Ryan Chong I have a question, I need the codes to recalculate the sheets after the pasting, where can I add in
ActiveSheet.Calculate

Open in new window

or is the following code already doing this for me?
Application.ScreenUpdating = True

Open in new window

Ryan ChongSoftware Team LeadCommented:
Application.ScreenUpdating = True

Open in new window


is not exactly do the calculation for you if you set the Calculation Options to Manual
Application.ScreenUpdating is to prevent the screen to update and do the calculations (if Calculation Options set to Automatic)

hence, to refresh the calculation, you do like this:
ActiveSheet.Calculate
Application.ScreenUpdating = True

Open in new window

An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
I see, where can I put the
ActiveSheet.Calculate

Open in new window

in the Sub CopyPasteValuesInTab(ByVal SheetName As String) subroutine?

I think that this way, I can make it recalculate everything since it is calling for it to be calculated for each sheet.
Ryan ChongSoftware Team LeadCommented:
in the Sub CopyPasteValuesInTab(ByVal SheetName As String) subroutine?
Nope, at here instead.

Sub Button1_Click()
    Application.ScreenUpdating = False
    Call CopyPasteValuesInTab("Allocation")
    Call CopyPasteValuesInTab("By Ctry-EIN")
    Call CopyPasteValuesInTab("By Ctry-EMSB")
    Call CopyPasteValuesInTab("By Ctry-ETH")
    Call CopyPasteValuesInTab("By Ctry-EPC")
    Call CopyPasteValuesInTab("ESD Trf Qty")
    '...
    ActiveSheet.Calculate
    Application.ScreenUpdating = True
   
    MsgBox "Done!"
End Sub
An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
Alright, thank you so much for your help! I will go ahead to test the code first and get back to you as soon as possible :)
Fabrice LambertConsultingCommented:
@Ryan:
If you set a reference to a worksheet, there is no need to activate it (selecting and activating things provide a bad user experience).
Also, your code can be shortened, and don't forget explicit déclarations:
Sub CopyPasteValuesInTab(ByVal SheetName As String)
    Dim ws As Excel.Worksheet
    Set ws = Sheets(SheetName)

    ws.Cells.Copy
    ws.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Open in new window

An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
Hello everyone, I have tried both versions of the codes provided and there seems to be a similiar issue.

I have put the code into a userform and encoded it into a button. However, I believe that there is an error in the code as this appears
1.JPG
And the error is this line
2.JPG
Ryan ChongSoftware Team LeadCommented:
try add:

Dim ws as worksheet

Open in new window


into your code?

and make sure you passing a valid Sheet Name?
An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
I have tried that and the error still persists
An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
Do you mean at this part
Set ws = Sheets(SheetName)

Open in new window

I should change SheetName to something else?
Ryan ChongSoftware Team LeadCommented:
I should change SheetName to something else?

well, it depends on what you going to do.

Set ws = Sheets(SheetName)

Open in new window


so in the case above, you are passing SheetName to refer to an existing worksheet. hence, the SheetName must refer to an existing Sheet Name. if you're passing an invalid Sheet Name, code above will get the error.
Ryan ChongSoftware Team LeadCommented:
or ... you can share your latest Excel file with the UserForm, we will try to debug it.
An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
Okay, here it is :)
An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
Ryan ChongSoftware Team LeadCommented:
in this sample file PART-1---REFRESH-COPY---PASTE.xlsm:

you are calling:

Call CopyPasteValuesInTab("Allocation")
    Call CopyPasteValuesInTab("By Ctry-EIN")
    Call CopyPasteValuesInTab("By Ctry-EMSB")
    Call CopyPasteValuesInTab("By Ctry-ETH")
    Call CopyPasteValuesInTab("By Ctry-EPC")
    Call CopyPasteValuesInTab("ESD Trf Qty")

Open in new window


while the fact that there is no worksheet with name:

  • Allocation
  • By Ctry-EIN
  • By Ctry-EMSB
  • By Ctry-ETH
  • By Ctry-EPC
  • ESD Trf Qty

you may add to have a better error handling, like:

Sub CopyPasteValuesInTab(ByVal SheetName As String)
    On Error GoTo EH
    Dim ws As Worksheet
    Set ws = Sheets(SheetName)
    ws.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    Exit Sub
EH:
    Debug.Print Err.Number & ": " & Err.Description
    Application.CutCopyMode = False
End Sub

Open in new window

An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
Oh I see, thanks! By any chance do you know how I can run a userform which is in one workbook onto another workbook?
Ryan ChongSoftware Team LeadCommented:
By any chance do you know how I can run a userform which is in one workbook onto another workbook?
technically this can be done if both workbooks are loaded

we may continue to discuss this in your new question.
An Average Forum Participant Just For FunHardware Tester and DebuggerAuthor Commented:
Alright, I will post it by today :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.