+ Reply to Thread
Results 1 to 22 of 22

VBA Code to transfer data from one sheet to another

  1. #1
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    VBA Code to transfer data from one sheet to another

    I need help with this

    It only seems to transfer 1 row where as the data I have has numerous rows can somone help with this please.

    Code is below

    rivate Sub CommandButton3_Click()

    Dim TestName As String, StepName As String, Description As String, Expected As String, Actual As String, TestStatus As String, Comments As String
    Worksheets("Test Execution Sheet").Select

    TestName = Range("A2")

    StepName = Range("B2")

    Description = Range("C2")

    Expected = Range("D2")

    Actual = Range("E2")

    TestStatus = Range("F2")

    Comments = Range("G2")

    Worksheets("data").Select
    Worksheets("data").Range("a1").Select
    If Worksheets("data").Range("a1").Offset(1, 0) <> "" Then
    Worksheets("data").Range("a1").End(xlDown).Select

    End If

    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = TestName
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = StepName
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Description
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Expected
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Actual
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TestStatus
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Comments
    Worksheets("Test Execution Sheet").Select


    End Sub

    The data sheet has the same table where no data is present I want the data I submit from Test Execution Sheet to transfer to the data sheet howwever this only seems to transfer one row an not all rows in view within the Test Execution Sheet.
    Attached Files Attached Files
    Last edited by Mick1982; 01-10-2019 at 12:41 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    It would help considerably if you were to upload the workbook and manually add the results you expect to see in another sheet.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    I cant seem to upload the workbook sorry

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    Quote Originally Posted by Mick1982 View Post
    I cant seem to upload the workbook sorry
    Edit your OP by using the Go Advanced button at the bottom right of your original post, then look underneath the post area for 'Manage Attachments' and take it from there

  5. #5
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Hi Richard this has been added

    Basically and data in the table on the sheet test execution sheet, when I click the command button 3 button I want all the rows to copy over, currently it only copies the 1st row.

    Thanks

  6. #6
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Can any one assist or provide any answers to this one please?

    Thanks in advance.

    Mike

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    Please explain in words exactly how you use this and what you expect to happen. It's not at all clear.

    Indicate which is original data and manually add a set of results and identify those. Most importantly explain how you arrived at the results, e.g. what were the inputs?

    Don't simply write down what your current macros are attempting to do. Let us decide from basic principles what the optimum solution is.

    In your current code there are in any case far too many .Select and .Activate instructions which make any macros inefficient, and in any case , ;et us decide wh

  8. #8
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Hi Richard

    On the test execution sheet the is table which shows 4 rows of data.

    Capture.PNG

    When I click the command button 3 I want all the rows to transfer to the data sheet however this only seems to transfer the 1 row every time if there is an easier way to control this please let me know.

    Capture 2.PNG

    Thanks Mike

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    Do you really mean that every single row on the Test Execution sheet is to be copied to the data sheet so that the Data sheet is Exactly the same as the Test Execution sheet? In which case why? That doesn't give you any additional information.

    I feel there is some information missing here. Perhaps the Data Sheet is a cumulative list of records whereby the Test Execution sheet changes periodically and you want to add NEW records to the Data Sheet.

    Perhaps the Test Data Pass/Fail is significant and you only want to transfer the Pass status to the records to the Data sheet

    If so you need to clearly explain.

    In any case this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see for a series of events. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

    That said if the ultimate task is to record data and then report various sub sets of that data, why not keep everything on one sheet and use either the Filter functionality (either an AutoFilter or Advanced Filter), or probably better still a Pivot Table.

  10. #10
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Hi Richard

    The text execution sheet data will change based on selecting a test case from the button choose test to run and selecting the test case from the drop down. When clicking back the test selected is pulled through. Once columns e,f,g have been completed, I want to be able to submit the results using command button 3 to the data sheet to store this to be able to report out on.

    So I guess the data sheet would be a cumulative list of records whereby the Test Execution sheet changes and you want to add NEW records to the Data Sheet.

    Hope this makes sense.

    Mike

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    So do you mean that whenever you press the command button 3 EVERY record on the Test Execution sheet is moved to the Data sheet? That implies that you'd also want to clear the contents of the Test Execution sheet when they have been copied ready for the next input?

    However if that is true why bother with all that. Why not just add new records to a database and use the filtering functionality to see a subset of the data that you're interested in. I generally have a single data entry row above the database, create a new record in that row and click a button to copy it into the database. i.e. this is essentially the same as what you do now but doesn't involve two sheets.

    Are the two hidden sheets of any relevance?

  12. #12
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Hi Richard

    The choose test to run sheet controls what is pulled through to columns A,B,C,D along with the rows of the test execution sheet.

    Then E,F,G of the test execution sheet cells are blank, as this is where the user would update results, I only want to clear contents from these as columns A,B,C,D pull data from another source and I do not want the code to be removed.


    I have it working but it only transfers 1 row, all I am asking, is there away to ensure this transfers all the rows.

    Thanks

    M
    Last edited by Mick1982; 01-11-2019 at 09:51 AM.

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    The simplest way is

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    This does not seem to work sorry
    Last edited by Mick1982; 01-11-2019 at 01:00 PM.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Code to transfer data from one sheet to another

    Try this:
    Please Login or Register  to view this content.
    Last edited by xladept; 01-11-2019 at 08:44 PM. Reason: More efficient code.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  16. #16
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    Quote Originally Posted by Mick1982 View Post
    This does not seem to work sorry
    It seems an incredible long winded way of identifying a subset of records on the Choose Test To Run sheets columns A:D, adding three fields of information and copying to a cumulative database.

    The attached workbook uses just two small macros, two range names and a dynamic range name
    These macros are run by clicking the new blue buttons. One on the Choose Test To Run sheet and the other on the Test Execution Sheet. The columns L:Q on the test to run sheet are not needed

    These are the macros in Module1

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

  17. #17
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Hi Richard

    Thanks for your assistance with this one.


  18. #18
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Sorry to be a pain Richard can you confirm how to extend the copy filter feature to incuded further columns and data from the choose test to to run sheet??

    Also the update tests seem to pull through numerous tests, I only want the select tests in the choose tests columns n-r to pull through based on the selected test.

    It seems if I select TC1 it pulls all test cases where there is TC 1 included even though some are TC 11, TC 12, this happens when selecting TC 2 too it pulls TC 21 TC 22.

    I wont each TC to be copied over based on its test so TC 1 copies only TC 1 data, TC 2 only copies TC 2 data etc.

    Hope this make sense
    Attached Files Attached Files
    Last edited by Mick1982; 01-14-2019 at 12:53 PM. Reason: Additional info

  19. #19
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    The attached will now filter the exact match. e.g. only TC1 not TC11

    You can include extra columns simply by ensuring you use the same labels in the outout range. Currently that name is "DataOut" and it's defined as
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    i.e. columns A:D

    If you want to include say the Cycle and Tests column labels in E4:F4 of the Choose Test to run sheet then those labels need to be within the DataOut range definition. i.e. you'd need to change E1 to say "Cycle" and F1 to "Tests" (or change F4 to "Test Status", and change G1 to "Actual" and ensur column G is NO within the outout range since i doesn;t exist in the data range. i..c hange the DataOut ange name to

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Thanks for your help with this.

    How can I view the current dataout range?
    Last edited by Mick1982; 01-15-2019 at 05:46 AM.

  21. #21
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Code to transfer data from one sheet to another

    DataOut is a range name which you can see by looking in the Name Manager on the Ribbon. You'll see it's currently defined as

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If the dataout range is likely to change then you should make it into a dynamic range name so that it automatically adjusts. I doubt this is necessary here since once you've redefined it to cover A1:F1 that will suffice.

  22. #22
    Registered User
    Join Date
    01-10-2019
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    12

    Re: VBA Code to transfer data from one sheet to another

    Hi Richard

    Consider this as complete and solved.

    Thanks very much for your assistance.

    Regards

    Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Code for create Sheet and transfer Data
    By hkbhansali in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-02-2017, 01:24 PM
  2. code that allow transfer data from userform to sheet
    By mazan2010 in forum Excel Programming / VBA / Macros
    Replies: 42
    Last Post: 01-31-2017, 04:55 PM
  3. Require VBA code to Transfer specific data from one sheet to another sheet.
    By xlhelp7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2017, 10:20 AM
  4. Using VBA Code and checkBoxes to transfer data to another sheet
    By carlandtina02 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-26-2012, 05:50 PM
  5. vb code - Automatic transfer of data from one sheet to another
    By herr_bench in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-23-2005, 08:20 PM
  6. vb code - automatic transfer of data from one sheet to another
    By herr_bench in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-23-2005, 06:48 AM
  7. vb code - Automatic transfer of data from one sheet to another
    By herr_bench in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-23-2005, 03:11 AM

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