Need To extract data from a column to another worksheet.
Closed
smv1
Posts
2
Registration date
Tuesday March 21, 2017
Status
Member
Last seen
March 22, 2017
-
Mar 21, 2017 at 03:48 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 22, 2017 at 09:10 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 22, 2017 at 09:10 AM
Related:
- Need To extract data from a column to another worksheet.
- Transfer data from one excel worksheet to another automatically - Guide
- How do you add a username or author to a worksheet - Guide
- Tmobile data check - Guide
- Extract audio from video vlc - Guide
- How to be offline in whatsapp when data is on - Guide
3 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Mar 22, 2017 at 08:20 AM
Mar 22, 2017 at 08:20 AM
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:-
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.
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.
smv1
Posts
2
Registration date
Tuesday March 21, 2017
Status
Member
Last seen
March 22, 2017
Mar 22, 2017 at 08:57 AM
Mar 22, 2017 at 08:57 AM
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?
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?
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Mar 22, 2017 at 09:10 AM
Mar 22, 2017 at 09:10 AM
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.
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.
