Posts by yongle

  • Move all files from a folder to the most recently created sub folder in another destination

    The code should now do exactly what you want

    Variables used to reduce repitition in the code


  • Move all files from a folder to the most recently created sub folder in another destination


    The new path name is now inconsistent because of adding the customer name to the folder names


    Name oldPath & StrFile As newPath & StrFile


    If need help in correcting the string, please post the whole of your amended code

  • Move all files from a folder to the most recently created sub folder in another destination

    Quote from G Velkov

    The last thing was my original request form the other Thread, is it possible to add the name of the customer to the names of the folders "Orders" and "Jobsheets"

    Test this

    Code
    replace:
MkDir FolderPath & ActiveCell.Value & "\Orders"
with:
MkDir FolderPath & ActiveCell.Value & "\Orders" & " - " & ActiveCell.Value

replace:
MkDir FolderPath & ActiveCell.Value & "\Jobsheets"
with:
MkDir FolderPath & ActiveCell.Value & "\Jobsheets" " - " & ActiveCell.Value

  • Move all files from a folder to the most recently created sub folder in another destination

    Quote from G Velkov

    Could be beneficial if instead of the hyperlink being created on the text in the selected cell, to be in another cell, let's say in D column on the same row.

    Also to see the resulting path visible as text: "C:\Users\"Username\Desktop\Customers\John Smith" still hyperlinked but just to the customer's name level, not the subfolders

    Test this

    Code
    replace:
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=FolderPath & ActiveCell.Value

with:
ActiveSheet.Hyperlinks.Add Anchor:=Range("D" & ActiveCell.Row), Address:=FolderPath & ActiveCell, TextToDisplay:=FolderPath & ActiveCell

  • Move all files from a folder to the most recently created sub folder in another destination

    Quote from G Velkov

    The behaviour that I have mentioned is observed only if we don't follow the routine of Operator to scan files, then select cell, click button -> Excel creates folders, subfolders, creates hyperlink, moves files, etc.

    I noticed it when I forgot to put files into the source.

    You could prevent the folders being created if the source folder is empty when the macro is run

    Put something like this early in the Command Button procedure

    Code
        If Dir("folder\subfolder\sourcefolder\*.*") = "" Then
        MsgBox "Source contains no visible files"
        Exit Sub
    End If
        
    MsgBox "carry on"
    code to create folders goes here

  • Move all files from a folder to the most recently created sub folder in another destination

    Quote from G Velkov

    I did notice that if we run the action and already have the folders generated but there was nothing in the source, and then we scan things and run it again, it breaks it. If the folders are deleted and the hyperlink removed though, then it works again. That is not concern because our routine needs to be the same and if we follow it, it works absolutely flawlessly.

    Before moving on to your "added extras", let's resolve the above

    1. Run MoveFiles when the sub-folder is created

    - is this already working flawlessly?


    2. Run MoveFiles again later

    Additional macro required containing this line

    Call MoveFiles("Here Enter the source folder path ending path with \","Here Enter the destination folder path ending path with \")

    The source path is constant

    The destination path is fixed EXCEPT for customer name ( DestinationFolderPath & "Customer Name" & "\Jobsheets" )

    - would a line in the macro asking the user to click on the cell containing CUSTOMER name work?

    - is there only one sheet in the workbook? (if not which sheet contains customer names matching folder names ?)


    Let me know your thoughts

  • Auto Add/Subtract

    The codes are almost identical and both clear the cell (with Target.ClearContents)


    The cell is not being cleared in YOUR workbook which suggests that something else is going on, that we are unaware of.


    1. Are the macros installed EXACTLY as posted ?

    2. Are any other event macros acting on your workbook ?


    If nothing is obvious to you, create a new workbook with only ONE sheet

    - test each one of the event macros in turn

    - do nothing other than amending values in J8:J400

    - I expect both macros to clear the cell

    - After that, look at your workbook and work out what is different

  • Move all files from a folder to the most recently created sub folder in another destination

    Try this on some test data


  • Auto Add/Subtract

    or this :)

  • Auto Add/Subtract

    must be done by vba


    right-click on sheet tab \ View Code \ paste code into the code window

  • how to export a multiline ms excel cell into a word table cell while preserving line breaks through vba

    Adapt code below to suit your needs

    More detailed information is required if you need further help


    Place the code below in the Word document module

    AND add reference to Microsoft Excel object library (see vba editor \ Tools \ References)

  • loop not working properly for adding textbox entries to last columns

    So - only ONE match , values updated in the SAME row, AND 3 (new) column values added when p increments by one

    (Error handling added to prevent code failing if match for strFind is not found)

  • Vlookup Another sheet VBA

    In UK

    Code
    On Error Resume Next
Result = WorksheetFunction.VLookup(Sheets("Sheet XX").Range("A6"), Sheets("Besoin de 3 semaines").Range("A:V"), 22, 0)
If Err.number <> 0 Then
'all is good
Else
'what happens if value in A6 is not found
End If


    Perhaps for you

    Result = Worksheetfunction.Vlookup(Sheets("Sheet XX").Range("A6");Sheets("Besoin de 3 semaines").range("A:V");22;0)


    NOTE

    You must include error handling

    If match is not found in Excel, the code shows #N/A

    If match is not found in VBA, the code fails and stops :(

  • loop not working properly for adding textbox entries to last columns

    Code is easier to read if you click on </> and post your code inside the code window


    My understanding

    strFind is the same value for every required match


    when p = 1

    and strFind is found

    ... the values in TB11, TB21 and TB31 are written to 3 new columns in the found cell's row


    p is now increased to 2

    Am I correct in thinking that you want the code to find the 2nd occurrence of strFind in column A

    and when strFind is found

    ... the values in TB12, TB22 and TB32 are written to 3 new columns in that row


    etc

    If not correct - please clarify what should happen when p = 2


    If my understanding is correct, test this amended code on a copy of your workbook


  • loop not working properly for adding textbox entries to last columns

    Insert this line

    Code
    Debug.Print p, c.Address(0,0)

    above line beginning lastcol = ...


    And look in the immediate window in VBA editor

    (Display immediate window with {CTRL} g


    Is VBA finding the same cell repeatedly ?


    .Find returns the first value only

    .FindNext is required to return all the other values


    See this link which shows how to use .Find and .FindNext together https://docs.microsoft.com/en-…/api/excel.range.findnext


    Happy to provide further help if you cannot resolve it.