Avatar of Noah
Noah
Flag for Singapore asked on

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 :)
VBA

Avatar of undefined
Last Comment
Noah

8/22/2022 - Mon
Ryan Chong

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
Noah

ASKER
@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".
ASKER CERTIFIED SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Noah

ASKER
@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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Ryan Chong

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

Noah

ASKER
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 Chong

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Noah

ASKER
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 Lambert

@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

Noah

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ryan Chong

try add:

Dim ws as worksheet

Open in new window


into your code?

and make sure you passing a valid Sheet Name?
Noah

ASKER
I have tried that and the error still persists
Noah

ASKER
Do you mean at this part
Set ws = Sheets(SheetName)

Open in new window

I should change SheetName to something else?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ryan Chong

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 Chong

or ... you can share your latest Excel file with the UserForm, we will try to debug it.
Noah

ASKER
Okay, here it is :)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Noah

ASKER
Ryan Chong

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

Noah

ASKER
Oh I see, thanks! By any chance do you know how I can run a userform which is in one workbook onto another workbook?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ryan Chong

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.
Noah

ASKER
Alright, I will post it by today :)