User Form to copy from one Worksheet to another

Kalin · Jul 11th 2017

Hello, I have struggled for 2 days with the attached form and finally got it working.
I will really appreciate, if someone can review and suggest improvements or just point out plain mistakes.
The objective was the following:

Find all excel files in a folder (XLS, XLSX, XLSM, CSV)
Pick one of the found files and copy particular data from it (worksheet => cells range)
Paste the data in another file from the same folder (worksheet => cell address)

Attached is the form (user_form_v5.xlsm). Your constructive criticism is more than welcome.

forum.ozgrid.com/index.php?attachment/73075/

**Ger Plante** · Jul 12th 2017

Re: User Form to copy from one Worksheet to another

Hi Kalin - Can you try uploading the file again... that file appears to be empty (for me at least!)

Kalin · Jul 12th 2017

Re: User Form to copy from one Worksheet to another

Hello Ger Plante,

The excel file is blank and when you open it and enable macros, the form is invoked automatically.
If you hit alt+f11, you should get to the editor part and the form should be there.
Anyway, I am attaching the exported form files, that have to be imported in a project to be used.

EDIT:
Unfortunately, I can't attach .FRM and .FRX files. I can post the code, but it's kind of useless without the actual form.
Please, try again the .XLSM file, the form is within it.
It might be Windows that's blocking it when you download it, as it comes from Internet and it has macros.
If that is the case, please right-click the file, click Properties and on the General tab, check "Unblock" hit OK and try it again.

**Ger Plante** · Jul 12th 2017

Re: User Form to copy from one Worksheet to another

Its really odd the Workbook_open even wont trigger (even after unblocking it etc.). It just opens Excel and doesnt even show a worksheet. However, if go through VBA and run the user form it works fine.

Anyway, did you write this yourself?

There is nothing blindingly inefficient about the code. If its working fine, I wouldnt necessarily go taking it apart.

There were one or two instances where you used .activate that I dont think you needed to use it... for example, when you open a workbook, then that becomes the "activewindow".

Code

Set src = Workbooks.Open(folder & "\" & CbSrcWrkbk.Value, True, True)
        src.Activate
        Application.WindowState = xlMinimized

Would it better just as?

Code

Set src = Workbooks.Open(folder & "\" & CbSrcWrkbk.Value, True, True)
        Activewindow.WindowState = xlMinimized

or maybe

Code

Set src = Workbooks.Open(folder & "\" & CbSrcWrkbk.Value, True, True)
        Activewindow.visible= false

Also consider opening the workbook in a hidden state...
https://stackoverflow.com/ques…-with-vba-without-display

But honestly, not really worth bothering about it.

Your function "Used_Range" doesnt actually return a value as part of the function name (which is what functions are for), you simply set (initialise) variables. I guess this is OK, but using it like this is effectively calling it a sub procedure.

Code

Function Used_Range()

Code

Sub Used_Range()

Again, no real impact

And staying with "Used_Range", since you are switching workbooks and worksheets, it would be "good practice" to tell the code what worksheet you are referring to when mentioned a range reference.

Code

If rng3 Is Nothing Then
        Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
        Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious)

which can be delivered as a parameter to the subroutine...

Code

Sub Used_Range(ws As Worksheet)
    
    With ws
        If rng3 Is Nothing Then
            Set rng1 = .Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
            Set rng2 = .Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious)


:
:
 end with

Display More

Otherwise, as far as I know, "Cells.find" will just look at the active sheet... which can not always be 100% guaranteed

Honestly though, code seems basically fine IMHO

Ger

Kalin · Jul 12th 2017

Re: User Form to copy from one Worksheet to another

Hi Ger Plante,

Thank you very much for taking time to review the code and for providing excellent feedback.

This is the second time I am using VBA for Excel, as a side task for our Finance staff.
My primary responsibilities are with the desktop environment and the server infrastructure behind it.
I wrote the code, using hints from here and there and it's really nice to hear that it's fine ::D
I will definitely look in your suggestions, as I would like to stick with the best practices and refer to the experience of others, no need to reinvent the wheel every day :smile:

Thank you again!

Kalin · Jul 12th 2017

Re: User Form to copy from one Worksheet to another

Hi Ger Plante,

Changing the Function to Sub is fine and skipping the src.Activate on two spots caused no harm. Unfortunately for the Used_Range subroutine, I can't pass the parameter as described by you.
The reason for src.Activate was to make sure that "Cells.find" will work as expected, but your explanation that by opening a workbook, it's also activated makes it not needed.
As for the "hidden" state, I prefer it to be minimized, as users might want to visualize the content of the worksheet.

I am having a slight issue - when I paste, the formatting (cells/font color, font, etc.) is lost. I wonder why?

Best regards,
Kalin

**Ger Plante** · Jul 12th 2017

Re: User Form to copy from one Worksheet to another

Nothing too obvious wrong with the paste function...

Code

Range(TxtDstCls.Value).Select
        Selection.PasteSpecial xlPasteAll

Again, there isnt a need to "select" the range here... this could be shortened to:

Code

Range(TxtDstCls.Value).PasteSpecial xlPasteAll

That being said, I dont this is the cause for formatting to be lost.

How about just doing:

Code

ActiveSheet.Paste Destination:=Range(TxtDstCls.Value)

?

Ger

Kalin · Jul 13th 2017

Re: User Form to copy from one Worksheet to another

Hi Ger,

Same result, however the finance guys is satisfied, as it's the data he needs.

Thank you once again for your attention, time and help!

Best regards,
Kalin

Kalin · Sep 1st 2017

Re: User Form to copy from one Worksheet to another

Thank you Jack,

While your solution works, its far from the subject discussed here.

Best regards,
Kalin

User Form to copy from one Worksheet to another

Participate now!

Share