Needs Help With VBA Automation of Keys

Noah
Noah used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

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

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".
Software Team Lead
Commented:
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

NoahHardware Tester and Debugger

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

Ryan ChongSoftware Team Lead

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

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.
Ryan ChongSoftware Team Lead

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

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

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
Ryan ChongSoftware Team Lead

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

Author

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

Author

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 Lead

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.
Ryan ChongSoftware Team Lead

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

Author

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

Author

Commented:
Ryan ChongSoftware Team Lead

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

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?
Ryan ChongSoftware Team Lead

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial