Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: VBA to transfer data from one workbook to another

  1. #1
    Board Regular
    Join Date
    Jun 2017
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to transfer data from one workbook to another

    With the help of others, I have created a workbook for our end users that allows them to submit spare part maintenance requests. The problem lies in the fact that we have 48 different locations that will be submitting these requests. Is it possible to create a command button that will only work for specified Windows user names, that when clicked will copy all of the data from the workbook to another workbook on a shared drive? In order to satisfy our internal audit we have to be able to track all of the changes, and having one location to look would take care of that.

  2. #2
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,649
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    Quote Originally Posted by Robert_Conklin View Post
    Is it possible to create a command button that will only work for specified Windows user names, that when clicked will copy all of the data from the workbook to another workbook on a shared drive?
    Yes, You can use Environ("USERNAME") to return the username of the individual currently logged into the computer. For example:

    Code:
    Public Sub TestUsername()
    Dim uName       As String
    uName = Environ("USERNAME")
    Select Case uName
        Case "John.Doe", "Jane.Doe", "Mr.Excel"
            'Do the things
        Case Else
            'Do Nothing
    End Select
    End Sub
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  3. #3
    Board Regular
    Join Date
    Jun 2017
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    GREAT! I have set up that portion of the code. Now how would I set up the "Do the things" and "Do Nothing"? My command button is not in a userform, but on a worksheet. The destination file is on a shared drive. When my end users send their parts maintenance workbooks in, they will undoubtedly have different names. I will have to open their file in order to use the "Submit" command button. So instead of specifying a specific source file, it would have to reference the active workbook. Thanks for your help!

  4. #4
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,649
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    You can link a button on the worksheet to a macro by right clicking on it and choosing "assign macro".

    For the "Do the Things" part of the code, I need more information.
    Where exactly is the destination file (what's the filepath)?
    What range in the source file should be copied?
    Where in the destination file should things be pasted?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  5. #5
    Board Regular
    Join Date
    Jun 2017
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    The source file is PARTS MAINTENANCE (LONG CODE) 2.0
    The Destination file is SPAR LOAD PROCESS WORKSHEET 2017 and is located at Z:\Engineering\Spar2\WinShuttle Daily Loads\SPAR LOAD PROCESS WORKSHEET 2017
    The columns from the "ADD-EXTEND" worksheet of the source file should transfer to the respective columns on the "RAW Data" worksheet of the destination file.

    I do not have permissions to attach the files on this site.

  6. #6
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,649
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    Try this out. Without knowing the structure of your data, I made the assumption that you want to copy the entire contents of "ADD-EXTEND" and paste them into "RAW Data", appending each paste to the bottom of the "RAW Data". This also uses column A in "ADD-EXTEND" to determine how far down your data goes, and row 1 in "ADD-EXTEND" to determine how many columns your data has.

    Code:
    Public Sub CopyData()
    Dim uName       As String
    
    Dim fPath       As String, _
        fName       As String
        
    Dim sWB         As Workbook, _
        sWS         As Worksheet, _
        dWB         As Workbook, _
        dWS         As Worksheet
        
    Dim sLR         As Long, _
        sLC         As Long, _
        dLR         As Long
        
    uName = Environ("USERNAME")
    
    fPath = "Z:\Engineering\Spar2\WinShuttle Daily Loads\SPAR LOAD PROCESS WORKSHEET 2017"
    fName = "SPAR LOAD PROCESS WORKSHEET 2017.xlsx"
    
    Select Case uName
        Case "John.Doe", "Jane.Doe", "Mr.Excel"
            'Do the things
            Set sWB = ThisWorkbook
            Set sWS = sWB.Sheets("ADD-EXTEND")
            
            Set dWB = Workbooks.Open(fPath & "\" & fName)
            Set dWS = dWB.Sheets("RAW Data")
            dLR = dWS.Range("A" & Rows.Count).End(xlUp).Row + 1
            
            sLR = sWS.Range("A" & Rows.Count).End(xlUp).Row
            sLC = sWS.Cells(1, Columns.Count).End(xlToLeft).Column
            sWS.Range(.Cells(1, 1), .Cells(sLR, sLC)).Copy Destination:=dWS.Range("A" & dLR)
                    
        Case Else
            'Do Nothing
    End Select
    End Sub
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  7. #7
    Board Regular
    Join Date
    Jun 2017
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    I placed the code and tested it, but received this error"

    [IMG]C:\Users\Robert.Conklin\Desktop\Submit Button Error Message[/IMG]

    Cannot run the macro ''Parts Maintenance (LONG CODE) 2.0.xlsb'!SUBMIT_Click'. The macro may not be available in this workbook or all macros may be disabled.
    Last edited by Robert_Conklin; Jul 18th, 2017 at 03:56 PM.

  8. #8
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,649
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    Quote Originally Posted by Robert_Conklin View Post
    I placed the code and tested it, but received this error"

    [IMG]C:\Users\Robert.Conklin\Desktop\Submit Button Error Message[/IMG]
    Try uploading the picture to a site like imgur.com and pasting the link to the image here.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  9. #9
    Board Regular
    Join Date
    Jun 2017
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    The error message reads:

    "Cannot run the macro ''PARTS MAINTENANCE (LONG CODE) 2.0.xlsb'!SUBMIT_Click'. The macro may not be available in this workbook or all macros may be disabled."

  10. #10
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,649
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to transfer data from one workbook to another

    You will need to save your "PARTS MAINTENANCE (LONG CODE) 2.0" workbook as a .xlsm file (Excel Macro-Enabled Workbook). Do that then try running the code
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •