VBA to transfer data from one workbook to another

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
173
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
I placed the code and tested it, but received this error"



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:
Upvote 0
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."
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top