+ Reply to Thread
Results 1 to 4 of 4

Using macro button to copy rows to separate sheet without duplicating entries

  1. #1
    Registered User
    Join Date
    03-22-2021
    Location
    South Dakota, USA
    MS-Off Ver
    365
    Posts
    2

    Using macro button to copy rows to separate sheet without duplicating entries

    I made a macro button that will copy rows based on bat species and paste them into a different sheet for each species. The button works as expected, but if I add new data to the main sheet (example data shown below), the next time I click the button it will add all the data for each species to their corresponding sheets, including old data that has already been entered. Is there a way to edit the code to overwrite cells that have a matching file name to avoid duplicates, or something to that effect? code shown below sample data. Thanks!

    File- Date- Time- Date and Time- Species- Species Code
    CRATR_20210220_185831- 2/20/2021- 18:56:31- 2/20/21 18:56:31- Big Brown Bat- 2
    CRATR_20210220_203858- 2/20/2021- 20:38:58- 2/20/21 20:38:58- Big Brown Bat- 2
    CRATR_20210220_204011- 2/20/2021- 20:40:11- 2/20/21 20:40:11- Big Brown Bat- 2
    CRATR_20210221_201502- 2/21/2021- 20:15:02- 2/21/21 20:15:02- Silver Haired Bat- 1
    CRATR_20210221_202415- 2/21/2021- 20:24:15- 2/21/21 20:24:15- Silver Haired Bat- 1
    CRATR_20210226_213920- 2/26/2021- 21:39:20 2/26/21 21:39:20- Silver Haired Bat- 1
    CRATR_20210301_200556- 3/1/2021- 20:05:56- 3/01/21 20:05:56- Silver Haired Bat- 1
    CRATR_20210301_214441- 3/1/2021- 21:44:41- 3/01/21 21:44:41- Western Small Footed Bat- 3
    CRATR_20210301_221213- 3/1/2021- 22:12:13- 3/01/21 22:12:13- Western Small Footed Bat- 3


    Private Sub CommandButton1_Click()

    a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a

    If Worksheets("Sheet1").Cells(i, 5).Value = "Silver Haired Bat" Then

    Worksheets("Sheet1").Rows(i).Copy
    Worksheets("Sheet3").Activate
    b = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet3").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Sheet1").Activate

    End If

    Next
    Application.CutCopyMode = False
    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

    End Sub
    Attached Files Attached Files
    Last edited by errlee; 03-22-2021 at 04:10 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: Using macro button to copy rows to separate sheet without duplicating entries

    Hi and welcome to the forum.

    What's the ultimate objective?

    If it's to view a subset of your data then is it really necessary to have a separate sheet for each species? Wouldn't a Pivot Table with a Slicer to display all the records for a particular species be preferable.

    If you really need to create separate sheets then personally, when this sort of incremental requirement is needed, I find it easier to first delete all the records on a sheet and recreate then afresh. It avoids the need to work out which are new records.
    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
    03-22-2021
    Location
    South Dakota, USA
    MS-Off Ver
    365
    Posts
    2
    Quote Originally Posted by Richard Buttrey View Post
    Hi and welcome to the forum.

    What's the ultimate objective?

    If it's to view a subset of your data then is it really necessary to have a separate sheet for each species? Wouldn't a Pivot Table with a Slicer to display all the records for a particular species be preferable.

    If you really need to create separate sheets then personally, when this sort of incremental requirement is needed, I find it easier to first delete all the records on a sheet and recreate then afresh. It avoids the need to work out which are new records.
    The objective is to have a sheet for each species so I can more easily graph each one in its own series. Deleting everything on each sheet before running the macro to add the new entries would work, but I would like to find a way to make it do that automatically even if only for the sake of getting better at coding. I guess the easiest way might be to have a line of code that will clear all the text in each of the species sheets before it copy and pastes all the entries from the main sheet. That would also solve the problem of having to manually edit if I review the sonograms and realize that I misidentified a big brown bat as a silver haired bat or something

  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: Using macro button to copy rows to separate sheet without duplicating entries

    Yes,

    The delete and recreate method requires the whole sheet be cleared (perhaps excluding a first row of labels) as a first line of code.

    I instinctively avoid duplicating subsets of a database over individual sheets merely to be able to go and look at a sheet. You can after all only look at one sheet at a time - leaving aside duplicate windows. It just seems to me more elegant not to say efficient.

    You could either use a data filter to extract the required data to a single report sheet, or even more efficiently use a Pivot Table with a slicer.

+ 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. code to copy first rows and sheet titles from separate csv files into summary sheet
    By freshmint in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-08-2021, 06:10 AM
  2. a macro on a button to switch between the text FALSE and TRUE on a separate sheet
    By Alunara_01 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-16-2015, 08:33 AM
  3. Duplicating rows to another sheet with update button problem
    By ericmday in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2014, 06:09 PM
  4. [SOLVED] Macro button copy specific rows from all worksheets to a summary sheet
    By cme in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-17-2013, 10:22 PM
  5. Replies: 0
    Last Post: 03-13-2013, 03:14 PM
  6. Replies: 2
    Last Post: 06-29-2012, 10:57 AM
  7. Copy rows into sheet according to entries in Column
    By Hblbs in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 03-16-2009, 10:56 AM

Tags for this Thread

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