1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

macro excel 2007 that copies filtered rows to another sheet

Discussion in 'Business Applications' started by aviflux, Oct 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. aviflux

    aviflux Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    18
    Hi,

    I'm new to this, so any help would be appreciated.

    My task is this - make checkboxes that would swoh only wanted rows and then copy any wanted rows to another sheet which has a specific design.

    So I have 2 columns "Description" and "price".To each row I made 15 checkboxes with macro that should remove unwanted rows using this macro:

    Sub CheckBox2_Click()
    Rows("1:3").Select
    If Selection.EntireRow.Hidden = False Then
    Selection.EntireRow.Hidden = True
    Else
    Selection.EntireRow.Hidden = False
    End If
    Range("$A$1").Select
    End Sub

    And this actually works (to my surprise), but the thing is I want to make a button that would have macro which would copy only checkboxed (marked) rows to another worksheet. I tried to record macros, but that didn't work because it seems that I have to link all checkboxes to that button and only then I'll get a result. Now it copies all rows, but not the marked ones.

    SO PLEASE HEEELP ME :)
     
  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    aviflux, welcome to the forum. :)

    Can you attach a sample file with dummy data?
     
  3. aviflux

    aviflux Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    18
    Here it goes. I know his is simple but i haven't used VBA in ages
     

    Attached Files:

  4. aviflux

    aviflux Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    18
    Oh and this is 2007 excel BTW. Forgot to change it.
     
  5. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I see that all the checkboxes are unchecked, but one. If I check the boxes, the rows are hidden.

    Are you trying to move the hidden rows, or the rows that are not hidden? Why is the last checkbox differrent than all the others? The last checkbox is already checked, and if I uncheck that box, the rows are hidden.
     
  6. aviflux

    aviflux Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    18
    well this is the problem, how do i write a a normal code that would hide the specifide rows (perhaps there is an easier way to do this), and then how to copy visibile ones (not hidden)to another sheet.

    My boss wants to me to do this, and he has no idea how to do this nor I do.
     
  7. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Let me see what I can come up with, and if I am uanble to do so there are plenty of people on here that can assist.
     
  8. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Explain to me what you are trying to achieve. For example, why are you moving data to another sheet? Will the data need to append to the second sheet, or each time you copy the data to the second sheet it replaces data that is there already? Do you want the data to be deleted from the first sheet was it has been moved?
     
  9. aviflux

    aviflux Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    18
    Ok thanks :)

    So the thing is that it's my boss idea. He thinks that if the data is moved to another sheet it would be less confusing for other people to use this data. And yes data form the first sheet should each time replace the data in the second sheet. The first sheet is like a draft, and the second one has a specific design (I removed it form example, because it's not relevant). When the data is imported to the second sheet, we will convet it to pdf. and will be send to clients.

    I perfectly understand that there is no need to use the second sheet, but he thinks it will be prettier this way :D
     
  10. aviflux

    aviflux Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    18
    Thanks Computerman29642, already found how to fix the codes :)
     
  11. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    That's great. :) (y)

    If you would not mind, please post the end result. Your solution may help someone else in the future.

    Also, please feel free to mark the thread as solved.
     
  12. aviflux

    aviflux Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    18
    First code to hide/unhide rows would be:

    Rows("19:21").EntireRow.Hidden = Not Rows("19:21").EntireRow.Hidden

    Second to copy everything to another sheet:

    On Error Resume Next
    Range("A1:B33").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
    On Error GoTo 0
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759379

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice