Extract Data from One Worksheet to Another

W

wirra

New Member
Joined
Aug 20, 2006
Messages
23
Hi,

I want to be able to extract/copy data from columns H (2017-18), M (First Name), & N (Last Name) from a worksheet called AllMembers based on Criteria Life Members and Financial only from column L (M'ship Type) and copy this data to another sheet called MemberSignIn in columns B (Last Name), C (First Name) & D (2017-18). This data is then to be sorted by Last Name, First Name. I need this automated with a formulae, macro or VBA so this process can be updated regularly by someone who doesn't have much Excel experience.

Hope this makes sense. Thanks for any help.



Excel Workbook
ABCDEFGHIJKLMNO
12010-112011-122012-20132013-142014-152015-162016-172017-18Receipt No.Member SinceMembership #M'ship TypeFirst NameLast NameAddress - Street address
229-Nov-1006-Jul-1102-Jul-1214-Oct-1326-Jul-1430-Jun-1519-Jul-1629-Nov-1010-0001UnfinancialBruceSmith53 Smith Street
324-Nov-1019-Jun-1113-Jun-1212-Jul-1301-Jul-1410-Aug-1511-Jul-1626-Jul-17N4624-Nov-1010-0002FinancialMiltonBrown42 Yellow Parade
429-Nov-1017-Jun-1110-Jun-1213-Aug-1302-Jul-1415-Jul-1501-Jan-0001-Jan-0029-Nov-1010-0003Life MemberGarryJones2 Green Court
529-Nov-1027-Jul-1113-Jun-1219-Jul-1315-Sep-1414-Aug-1503-Aug-1629-Nov-1010-0004UnfinancialJudyGreen14 Jones Street
617-Nov-1006-Jul-1113-Jun-1203-Jul-1301-Sep-1430-Jun-1518-Jan-1714-Sep-17N9617-Nov-1010-0005FinancialRossGreen14 Jones Street
717-Nov-1006-Jul-1113-Jun-1203-Jul-1302-Jul-1428-Jul-1505-Oct-1605-Jul-17L7917-Nov-1010-0006FinancialBenGreen2/33 Johns Street
817-Nov-1006-Jul-1117-Nov-1010-0007UnfinancialJosephBanks13 Good Street
917-Nov-1027-Jul-1126-Jun-1214-Aug-1304-Jun-1420-Aug-1509-May-1601-Jan-0017-Nov-1010-0008Life MemberChristineZoran8 Yellow Street
1017-Nov-1006-Jul-1126-Jun-1202-Jul-1304-Jun-1404-Jun-1501-Aug-1611-Sep-17N93 (joint)17-Nov-1010-0009FinancialJohnWells358 Milford Road
1117-Nov-1005-Oct-1111-Jul-1208-Jul-13one03-Jun-1528-Jun-1617-Nov-1010-0010UnfinancialPhillipAbraham256 Jubilee Terrace
1217-Nov-1017-Nov-1010-0011UnfinancialMichaelPhilips74 Ryder Road
13
14
15
16Last NameFirst Name2017-18
17BrownMilton26-Jul-17
18GreenBen05-Jul-17
19GreenRoss14-Sep-17
20JonesGarry01-Jan-00
21WellsJohn11-Sep-17
22ZoranChristine01-Jan-00
23
Sheet1
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
2010-112011-122012-20132013-142014-152015-162016-172017-18Receipt No.Member SinceMembership #M'ship TypeFirst NameLast NameAddress - Street address
29-Nov-1006-Jul-1102-Jul-1214-Oct-1326-Jul-1430-Jun-1519-Jul-1629-Nov-1010-0001UnfinancialBruceSmith53 Smith Street
24-Nov-1019-Jun-1113-Jun-1212-Jul-1301-Jul-1410-Aug-1511-Jul-1626-Jul-17N4624-Nov-1010-0002FinancialMiltonBrown42 Yellow Parade
29-Nov-1017-Jun-1110-Jun-1213-Aug-1302-Jul-1415-Jul-1501-Jan-0029-Nov-1010-0003Life MemberGarryJones2 Green Court
29-Nov-1027-Jul-1113-Jun-1219-Jul-1315-Sep-1414-Aug-1503-Aug-1629-Nov-1010-0004UnfinancialJudyGreen14 Jones Street
17-Nov-1006-Jul-1113-Jun-1203-Jul-1301-Sep-1430-Jun-1518-Jan-1714-Sep-17N9617-Nov-1010-0005FinancialRossGreen14 Jones Street
17-Nov-1006-Jul-1113-Jun-1203-Jul-1302-Jul-1428-Jul-1505-Oct-1605-Jul-17L7917-Nov-1010-0006FinancialBenGreen2/33 Johns Street
17-Nov-1006-Jul-1117-Nov-1010-0007UnfinancialJosephBanks13 Good Street
17-Nov-1027-Jul-1126-Jun-1214-Aug-1304-Jun-1420-Aug-1509-May-1617-Nov-1010-0008Life MemberChristineZoran8 Yellow Street
17-Nov-1006-Jul-1126-Jun-1202-Jul-1304-Jun-1404-Jun-1501-Aug-1611-Sep-17N93 (joint)17-Nov-1010-0009FinancialJohnWells358 Milford Road
17-Nov-1005-Oct-1111-Jul-1208-Jul-13one03-Jun-1528-Jun-1617-Nov-1010-0010UnfinancialPhillipAbraham256 Jubilee Terrace
17-Nov-1017-Nov-1010-0011UnfinancialMichaelPhilips74 Ryder Road
you have date errors in cols g and h
I made them in col H blank
BrownMilton26/07/2017
JonesGarry this macro pulls data for the lower table
GreenRoss14/09/2017
GreenBen05/07/2017you can add code for sorting by last name then first name, I am sure
ZoranChristine
WellsJohn11/09/2017Sub Macro4()
'
' Macro4 Macro
' Macro recorded 17/11/2017 by bob
'
'
Dim fname(20), lname(20), myyear(20)
rrow = 19
For j = 2 To 12
If Cells(j, 12) = "Financial" Or Cells(j, 12) = "Life Member" Then GoTo 20 Else GoTo 50
20 Sum = Sum + 1
fname(Sum) = Cells(j, 13)
lname(Sum) = Cells(j, 14)
If Cells(j, 8) = "" Then myyear(Sum) = " ": GoTo 50
myyear(Sum) = Cells(j, 8)
50 Next j
For k = 1 To 11
tot = tot + 1
rrow = rrow + 1
If lname(tot) = "" Then GoTo 100
Cells(rrow, 2) = lname(tot)
Cells(rrow, 3) = fname(tot)
Cells(rrow, 4) = myyear(tot)
Next k
100 End Sub

<colgroup><col><col span="2"><col><col span="2"><col><col><col span="3"><col><col><col><col span="9"></colgroup><tbody>
</tbody>
 
Upvote 0
This macro assumes you have a sheet named "MemberSignIn" and that it has the headers in B1:D1
Code: 
Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomL As Long
    bottomL = Range("L" & Rows.Count).End(xlUp).Row
    Dim bottomL2 As Long
    Range("A1:O" & bottomL).AutoFilter Field:=12, Criteria1:="=Financial", Operator:=xlOr, Criteria2:="=Life Member"
    Range("N2:N" & bottomL).SpecialCells(xlCellTypeVisible).Copy Sheets("MemberSignIn").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
    Range("M2:M" & bottomL).SpecialCells(xlCellTypeVisible).Copy Sheets("MemberSignIn").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
    Range("D2:D" & bottomL).SpecialCells(xlCellTypeVisible).Copy Sheets("MemberSignIn").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
    bottomL2 = Sheets("MemberSignIn").Range("B" & Rows.Count).End(xlUp).Row
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Sheets("MemberSignIn").Sort.SortFields.Clear
    Sheets("MemberSignIn").Sort.SortFields.Add Key:=Range( _
        "B2:B" & bottomL2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    Sheets("MemberSignIn").Sort.SortFields.Add Key:=Range( _
        "C2:C" & bottomL2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Sheets("MemberSignIn").Sort
        .SetRange Range("B1:D" & bottomL2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks mumps and oldbrewer.

mumps - My actual data on sheet AllMembers has columns A to Z so I changed your code for line 6 to read Range("A1:Z" - is this correct? What does Field:=12 refer to? I don't know much about VBA so just guessing here.
Also there could be a mistake in line 9 which I changed from "D2:D" to "H2:H". Does that make sense?

A couple of more questions:

1. I initially ran your macro from sheet MemberSignIn and it produced an error. It worked correctly when run from sheet AllMembers. I assume from this that I have to be in sheet AllMembers to run the macro. Is this correct? Is there anyway it can be run from sheet MemberSignIn?

2. When run, the macro copies any formatting from AllMembers sheet eg. cell fill colours. Can it be changed to exclude formatting from sheet AllMembers?

3.If I run the macro without first clearing the data in sheet MemberSignIn it adds the new data therefore duplicating the data. Is there a way to clear the data in MemberSignIn columns B to D before adding the updated data?

Once again, thanks very much for your help.
 
Upvote 0
The changes you made are correct. The "Field:=12" refers to the column number that is used to autofilter the data. In your case column 12 or column L, contains the M'ship Type. This macro should take care of all three questions that you had. Give it a try.
Code: 
Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomL As Long
    bottomL = Sheets("AllMembers").Range("L" & Rows.Count).End(xlUp).Row
    Dim bottomL2 As Long
    Sheets("MemberSignIn").UsedRange.Offset(1, 0).ClearContents
    Sheets("AllMembers").Range("A1:Z" & bottomL).AutoFilter Field:=12, Criteria1:="=Financial", Operator:=xlOr, Criteria2:="=Life Member"
    Sheets("AllMembers").Range("N2:N" & bottomL).SpecialCells(xlCellTypeVisible).Copy
    Sheets("MemberSignIn").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("AllMembers").Range("M2:M" & bottomL).SpecialCells(xlCellTypeVisible).Copy
    Sheets("MemberSignIn").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("AllMembers").Range("H2:H" & bottomL).SpecialCells(xlCellTypeVisible).Copy
    Sheets("MemberSignIn").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    bottomL2 = Sheets("MemberSignIn").Range("B" & Rows.Count).End(xlUp).Row
    Application.CutCopyMode = False
    If Sheets("AllMembers").AutoFilterMode = True Then Sheets("AllMembers").AutoFilterMode = False
    Sheets("MemberSignIn").Sort.SortFields.Clear
    Sheets("MemberSignIn").Sort.SortFields.Add Key:=Range( _
        "B2:B" & bottomL2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    Sheets("MemberSignIn").Sort.SortFields.Add Key:=Range( _
        "C2:C" & bottomL2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Sheets("MemberSignIn").Sort
        .SetRange Range("B1:D" & bottomL2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Thanks very much mumps. Works beautifully. Thanks for your time. It is very much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Setup Excel relationships to combine two data source
Replies
2
Views
376
sbawnh
S
willow1985
  • Solved
VBA copy set amount of rows into forms until end of data set
2
Replies
15
Views
215
willow1985
willow1985
T
  • Question
Extract data (Filter) from a table using criteria from a diferent table
Replies
5
Views
303
duggie33
D
D
  • Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
493
Herakles
H
D
  • Question
Using VBA/Macros to Duplicate Sheets and Inserting Individual Rows from Table
Replies
2
Views
613
donnington
D

Forum statistics

Threads
1,214,561
Messages
6,120,242
Members
448,951
Latest member
jennlynn

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top