We help IT Professionals succeed at work.

Needs Help With VBA Automation of Keys

Noah
Noah asked
on
59 Views
Last Modified: 2019-03-21
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 :)
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

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".
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

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

CERTIFIED EXPERT

Commented:
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

NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

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.
CERTIFIED EXPERT

Commented:
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
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

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 LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
@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

NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

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
CERTIFIED EXPERT

Commented:
try add:

Dim ws as worksheet

Open in new window


into your code?

and make sure you passing a valid Sheet Name?
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
I have tried that and the error still persists
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

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

Open in new window

I should change SheetName to something else?
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
or ... you can share your latest Excel file with the UserForm, we will try to debug it.
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
Okay, here it is :)
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
CERTIFIED EXPERT

Commented:
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

NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

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?
CERTIFIED EXPERT

Commented:
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.
NoahHardware Tester and Debugger
CERTIFIED EXPERT

Author

Commented:
Alright, I will post it by today :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.