+ Reply to Thread
Results 1 to 3 of 3

Autofilter, copy from sheet 1 and paste to sheet2, delete previous info on sheet 2

  1. #1
    Registered User
    Join Date
    05-30-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    4

    Autofilter, copy from sheet 1 and paste to sheet2, delete previous info on sheet 2

    Hello,

    I am new to VBA coding. I have a command button and would like it to copy whole rows anytime the words "Shuttle L", "Shuttle L & C", and "Team" appear in column A from sht1 and paste to sht2. Below is the code I found and it works for anytime the word Shuttle L appears. How do I change it so it works when the words "Shuttle L", "Shuttle L & C", and "Team" appear? Also is there a way to delete the previous information on sht2 so only the current info shows every time you click the button? (if what is pasting is smaller then what was pasted before the old data clears) Not sure if that makes any sense but any help would be appreciated.

    Thank you!

    Private Sub CommandButton21_Click()

    Dim sht1 As Worksheet, sht2 As Worksheet

    Set sht1 = Sheets("Fri AM (ALL)")
    Set sht2 = Sheets("Fri AM (L)")

    Intersect(sht2.UsedRange, sht2.Rows("2:" & Rows.Count)).ClearContents

    sht1.Cells(8, 1).CurrentRegion.AutoFilter
    sht1.Cells(8, 1).CurrentRegion.AutoFilter 1, "Shuttle L"
    sht1.Cells(8, 1).CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy sht2.Cells(8, 1)
    sht1.Cells(8, 1).CurrentRegion.AutoFilter

  2. #2
    Registered User
    Join Date
    10-20-2008
    Location
    USA
    Posts
    6

    Re: Autofilter, copy from sheet 1 and paste to sheet2, delete previous info on sheet 2

    try this:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-30-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    4

    Re: Autofilter, copy from sheet 1 and paste to sheet2, delete previous info on sheet 2

    Thanks so much!!! Works great!!

+ 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. Copy Info from Sheet to Another for 4 Previous Weeks
    By Timbollew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2014, 01:46 PM
  2. Copy Cell Range From Previous Sheet and Paste to Active Sheet
    By jtal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2013, 03:26 PM
  3. Copy/paste row with todays date in Sheet2 to sheet 3....but from Sheet 1
    By cammyjane10 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-25-2013, 01:07 PM
  4. [SOLVED] Cmd to Listbox (Find info in sheet2) copy/paste info to A7 Excel 2007
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-14-2013, 09:49 AM
  5. Filter-Copy Paste-Save sheet-Delete info
    By srikanthbenoni in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-28-2012, 06:08 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