+ Reply to Thread
Results 1 to 19 of 19

VBA button to store data from 1 workbook and paste it in another UN-opened workbook

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Uniontown, PA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Cool VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Hey everyone! First time forum user here! Anyway I am a very unskilled VBA user since I only had to do it for 1 class in college (which was years ago!). Anyway, I want to create 1 page called SuggestionSystem (Excel 2010) and have cells that people will fill in with production suggestions. I want a button on that page that when pushed all the data in SuggestionSystem cells will be stored in an un-opened page called SuggestionData. Would it be possible for someone to give me the entire code (with 'explanations) so that I could just copy and paste it? The cells with data on SuggestionSystem will be merged cells: F4:G4, L4:M4, R4:S4, F9:G9, P9:Q9, H14:O29. I would like these data points stored in 1 row in the new sheet with a +1 code that keeps them stored in order (one after another). I REALLY could use some help guys! thanks!!!
    Last edited by mountaineerhughes; 07-19-2012 at 11:25 AM. Reason: Update

  2. #2
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Could you provide a sample that I can manipulate, then give the sample back with code and button finished? I'm not trying to snoop, it's just easier to code with the forms 'in hand'.
    Click on the * icon if this post has been helpful.

  3. #3
    Registered User
    Join Date
    07-19-2012
    Location
    Uniontown, PA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Here are 2 basic layouts of what we may use. I was hoping to password protect the Data sheet so no one can enter the entire workbook without access. I would like the entered Date, Shift, Department, Employee, Supervisor, and Explanation to post and store in the data workbook upon hitting the button. I want to keep all posts so the code would have to know to keep storing on the next line down. Thanks!!!
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Here are the files back with code. The password to Data.xlsm is Password.

    The password is what is in red below, that needs to be what ever you set the password to. Not the protect workbook password, but the open password.
    If you have any questions let me know.

    Code:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-19-2012
    Location
    Uniontown, PA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Whenever I enter new values in and click the command button I do not get the new data uploaded into the Data spreadsheet. The message box displays Transfer Completed but nothing comes up in the Data sheet. Any Thoughts???

  6. #6
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Are both excel documents saved to the same path?
    Please Login or Register  to view this content.
    is assuming both files are saved to the same folder on your computer.

  7. #7
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Right. Sorry I didn't explain that before. If they aren't in the same folder, let me know and I will fix the code to use your file path.
    Last edited by Cutter; 07-20-2012 at 08:47 PM. Reason: Removed whole post quote

  8. #8
    Registered User
    Join Date
    07-19-2012
    Location
    Uniontown, PA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Okay Xer2 I have an issue I need help with. The spreadsheet you gave me works great....absolutely no complaints and I truly appreciate it. I do, however, have another question for you. I don't know if you're familiar with sharepoint or not but I am trying to load both the SuggestionSystem and Data documents individually to a sharepoint library. I understand that Sharepoint doesn't allow you to run macros when opening excel files but it will let you open the file separately in excel. Whenever I open Suggestion System to excel and try to run my macro I get a run time error '52' error "Bad file name or number" and this line of code --> "If Dir(fileName) <> "" Then ".........any suggestions. The files are stored in the same folder on a network common drive so I don't see why they wont work just because I'm using sharepoint to open the file. I want my employees to be able to go to our sharepoint website, open only the SuggestionSystem spreadsheet, file it out, click submit, and the locked Data file have the suggestion stored. PLEASE HELP if you can.

  9. #9
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    I will start out by saying I have no experience with Sharepoint other than viewing it.

    Ok, with that said, as I understand it, even if the SuggestionSystem is opened as a standalone workbook it still won't work. Is that correct?

  10. #10
    Registered User
    Join Date
    07-19-2012
    Location
    Uniontown, PA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Actually Xer2 scratch that last question. I need more of your coding skills. Would it be possible to take the SuggestionSystem workbook and use the button to save to another tab of the same SuggestionSystem workbook and ignore the entire Data workbook. I know this is pretty simple but the trick I have is that I want the sheet that the info gets pasted to to be unable to open unless by password...basically I dont want anyone to see that tab (say sheet2) unless they have the password...even though they have access to use sheet1. Is this even possible to lock 1 tab completely? Would you mind setting this up for me?

  11. #11
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Ok, I got it. I'll add another layer to the security. Try this:
    Attached Files Attached Files
    Last edited by Xer2; 07-25-2012 at 04:30 PM.

  12. #12
    Registered User
    Join Date
    07-24-2012
    Location
    New York City
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Xer2,

    This looks really helpful and in the right direction for what I need.

    Would you be able to help me with a macro that is the reverse of this? I run it in one Summary file and it pulls data from three separate files? I just want all the data on the first sheet of the three pages to be copied into the corresponding sheet in the summary file.

    Summary.xlsm
    OppGrat.xlsm
    Q006.xlsm
    Q008.xlsm

    Thanks!
    Andy

  13. #13
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    @ striderider7
    Make sure this is in the same folder as the other files and try this:
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-24-2012
    Location
    New York City
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Thanks!

    I get an error sometimes that points at this line of code. Does that mean I need to always change the sheet name in the data sheets to be Sheet1?

    Set dataWs = dataWb(i).Sheets("Sheet1")

  15. #15
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Yes, or change the code and all sheets to the same thing.

  16. #16
    Registered User
    Join Date
    07-19-2012
    Location
    Uniontown, PA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Xer2 this is crazy awesome!

    Thanks!!!!!

    ---------- Post added at 08:06 AM ---------- Previous post was at 07:21 AM ----------

    Xer2,

    Would it be possible for you to change the lock and unlock passwords to "ME".....both capital?

    ---------- Post added at 08:06 AM ---------- Previous post was at 08:06 AM ----------

    Xer2,

    Would it be possible for you to change the lock and unlock passwords to "ME".....both capital?

  17. #17
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Here you go. In the future, you can change the password through the VBA. If you are unsure about how, just let me know.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    07-19-2012
    Location
    Uniontown, PA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Xer2,

    I've attached a cleaned up portion of the spreadsheet (which by the way works awesome). Would it be possible for you to make one more change. Could you add a restriction in the code so that when someone submits a suggestion they can't keep pushing the button and storing the same data. Is there a way to limit repeating submissions over and over?
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: VBA button to store data from 1 workbook and paste it in another UN-opened workbook

    Try this version of the workbook.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1