Posts by Kalin

  • User Form to copy from one Worksheet to another

    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

  • User Form to copy from one Worksheet to another

    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!

  • User Form to copy from one Worksheet to another

    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.

  • User Form to copy from one Worksheet to another

    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/

  • VBA, PrintArea, Selection - unable to exclude cells with formulas

    Re: VBA, PrintArea, Selection - unable to exclude cells with formulas


    While I was waiting for help, I helped my self :)


    Solution:

    Code
    Sub PDF_Kalin_Information() 
    ThisWorkbook.Sheets(Array("Open Finance - Kalin", "Completed Finance - Kalin", "Open General - Kalin", "Completed General - Kalin")).Select
    Call Set_Print_Area
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="W:\Test\test.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True     
End Sub


    Code
    Function Set_Print_Area()
Dim s As Worksheet
Dim lastCell As Long
    For Each s In ActiveWindow.SelectedSheets
         lastCell = [LOOKUP(2,1/(A1:A65536<>""),ROW(A1:A65536))]
         s.PageSetup.PrintArea = "$A$1:$Q$" & lastCell
    Next
End Function


    Hope this will quickly help someone :)

  • VBA, PrintArea, Selection - unable to exclude cells with formulas

    Hello all,


    I have no prior experience with Excel and I was asked for assistance by one of our Finance staff members.
    Basically, there is a workbook, which consists of multiple worksheets.
    There are several people, that have 4 worksheets assigned to them.
    The first worksheet is like a control page, where they can see their names with a button to export their worksheets to PDF.
    The columns in the worksheets are fixed A-Q, but the rows count varies.
    The original action behind the button was:


    The problem is that there are cells that have some formulas (functions?), but they appear blank, like below:
    [ATTACH=CONFIG]72823[/ATTACH]


    These cells are included in the selection that is being exported and we would like avoid that, although they are not visible on the exported PDF file.
    So far, I have tried numerous ways, but every time in the Page Break view, I can see that the print area and the selection is including these cells, it works as expected on the first worksheet.
    This is what I currently have:



    And the Set_Print_Area() sub:



    I will really appreciate, if you can give me any directions or solve the problem for me :)


    Thank you in advance,
    Kalin