+ Reply to Thread
Results 1 to 3 of 3

Copy and pasting a range using autofilter and input box

  1. #1
    Registered User
    Join Date
    09-17-2014
    Location
    Cambridge, England
    MS-Off Ver
    2016
    Posts
    58

    Copy and pasting a range using autofilter and input box

    Hello, I currently run a report that contain an entire year of sales data. On a secondary tab I have a summary page where the user can input a date range so that the summary page just shows a snippet of the yearly data.

    Currently the macro works fine, but missed the last column off the data. Any ideas?

    The macro code is as follows....
    'start date define
    currentd2 = DateValue(InputBox("Start Date?", "Valid Format - dd/mm/yyyy", currentd))
    'end date define
    currentd3 = DateValue(InputBox("End Date?", "Valid Format - dd/mm/yyyy", currentd))

    Range("c2").Select

    Selection.AutoFilter Field:=6, Criteria1:=">=" & Format$(currentd2, "mm/dd/yyyy"), Operator:=xlAnd, _
    Criteria2:="<=" & Format$(currentd3, "mm/dd/yyyy")

    Rows("2:" & lr).Select
    Selection.Copy
    Sheets("Daily & Weekly Summary").Select
    Range("A2").Select
    Selection.Insert Shift:=xlDown
    'turn off cut copy mode and hide worksheets
    Range("A1").Select
    Application.CutCopyMode = False

    Any thoughts?

    The number of rows are fine, it is simply cutting of the final column (column U)

    Any help would be great.

  2. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Copy and pasting a range using autofilter and input box

    It's difficult to say for sure without seeing the data, but I'd guess maybe column U has a formula in that doesn't calculate properly outside of it's current location?

    Maybe put the following code in after line Selection.Insert Shift:=xlDown:

    activesheet.pastespecial xlvalues

    ?

  3. #3
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Copy and pasting a range using autofilter and input box

    The possibilities is your copying data from sheet that sheet does not have any data in column U as your inserting copying values in sheet(Daily & Weekly Summary")

    I guess....
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

+ 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. Problem in Copy Pasting Autofilter Data from One Sheet to Another
    By shivboy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-30-2014, 03:35 AM
  2. Replies: 2
    Last Post: 08-10-2014, 11:53 AM
  3. Autofilter based on input from a range on a worksheet
    By D.Lovell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2014, 07:09 PM
  4. Autofilter to select a range from user input
    By santu03 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2013, 12:11 AM
  5. autofilter copy to new range
    By Monique in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2005, 05:05 PM

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