Need To extract data from a column to another worksheet. [Closed]

Report
Posts
2
Registration date
Tuesday March 21, 2017
Status
Member
Last seen
March 22, 2017
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,

I need code to extract all data containing a name from a column on sheet1 and copy it to Sheet2 and remove all blanks. The name is pulled for a drop-down list of names on Sheet2. To supplement the filter process, I have a start date and end date on Sheet2 that I would like to use to condense the information retrieved.

Thanks,

Steve


3 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Steve,

Obviously we can't tell from your description how your work book is set out hence I've made some assumptions and believe that the following code, placed in a standard module and assigned to a button, may work for you:-


Sub Transfer()

    Dim lr As Long
    Dim StartDate As Long: StartDate = Sheet2.[B1]
    Dim EndDate As Long: EndDate = Sheet2.[E1]
    Dim cNm As String: cNm = Sheet2.[G1]
    
Application.ScreenUpdating = False

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    Sheet1.Range("A1:I" & lr).AutoFilter 1, cNm
    Sheet1.Range("A1:I" & lr).AutoFilter 2, ">=" & StartDate, xlAnd, "<=" & EndDate, 7
    Sheet1.Range("A2", Sheet1.Range("I" & Sheet1.Rows.Count).End(xlUp)).Copy
    Sheet2.Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
    Sheet1.[B1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


I've prepared a little sample for you to peruse at the following link:-

https://www.dropbox.com/s/vipp0bfkb22jw88/Smv1%28Autofilter%20between%20dates%2C%20names%29.xlsm?dl=0

In Sheet2 you will see a Start Date in cell B1, an End Date in cell E1 and a name box in cell G1 (which represents your drop down cell even though I haven't created a drop down list in this cell). For now, in the sample, type a name in G1, select start and end dates from those listed in Sheet1, then click on the "RUN" button to see it all work. Mix it up as you like to see if the code does the task for you.

Change the cell and range references to suit your actual work book and, if you choose to try it in your actual work book, be sure to test it all in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.
Posts
2
Registration date
Tuesday March 21, 2017
Status
Member
Last seen
March 22, 2017

I was unable to see the example sheet you sent. But I did
copy the code to a module and changed the references for cells and sheets. The code stalls on the second line : StartDate = Sheet2. [B1].

Any thoughts?
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Steve,

Without seeing your work book, its difficult to determine why an error would occur on that line.

What is the error message that comes up?

It would be best if you could upload a sample of your work book with the code implemented (be careful with any sensitive data) to a free file sharing site such as DropBox, ge.tt or Sendspace and then post the link to your file back here. We can then try to determine the best solution for you.

Cheerio,
vcoolio.