Extract Data from One Worksheet to Another

wirra · Nov 17, 2017

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2010-11

2011-12

2012-2013

2013-14

2014-15

2015-16

2016-17

2017-18

Receipt No.

Member Since

Membership #

M'ship Type

First Name

Last Name

Address - Street address

2

29-Nov-10

06-Jul-11

02-Jul-12

14-Oct-13

26-Jul-14

30-Jun-15

19-Jul-16

29-Nov-10

10-0001

Unfinancial

Bruce

Smith

53 Smith Street

3

24-Nov-10

19-Jun-11

13-Jun-12

12-Jul-13

01-Jul-14

10-Aug-15

11-Jul-16

26-Jul-17

N46

24-Nov-10

10-0002

Financial

Milton

Brown

42 Yellow Parade

4

29-Nov-10

17-Jun-11

10-Jun-12

13-Aug-13

02-Jul-14

15-Jul-15

01-Jan-00

29-Nov-10

10-0003

Life Member

Garry

Jones

2 Green Court

5

29-Nov-10

27-Jul-11

13-Jun-12

19-Jul-13

15-Sep-14

14-Aug-15

03-Aug-16

29-Nov-10

10-0004

Unfinancial

Judy

Green

14 Jones Street

6

17-Nov-10

06-Jul-11

13-Jun-12

03-Jul-13

01-Sep-14

30-Jun-15

18-Jan-17

14-Sep-17

N96

17-Nov-10

10-0005

Financial

Ross

Green

14 Jones Street

7

17-Nov-10

06-Jul-11

13-Jun-12

03-Jul-13

02-Jul-14

28-Jul-15

05-Oct-16

05-Jul-17

L79

17-Nov-10

10-0006

Financial

Ben

Green

2/33 Johns Street

8

17-Nov-10

06-Jul-11

17-Nov-10

10-0007

Unfinancial

Joseph

Banks

13 Good Street

9

17-Nov-10

27-Jul-11

26-Jun-12

14-Aug-13

04-Jun-14

20-Aug-15

09-May-16

01-Jan-00

17-Nov-10

10-0008

Life Member

Christine

Zoran

8 Yellow Street

10

17-Nov-10

06-Jul-11

26-Jun-12

02-Jul-13

04-Jun-14

04-Jun-15

01-Aug-16

11-Sep-17

N93 (joint)

17-Nov-10

10-0009

Financial

John

Wells

358 Milford Road

11

17-Nov-10

05-Oct-11

11-Jul-12

08-Jul-13

one

03-Jun-15

28-Jun-16

17-Nov-10

10-0010

Unfinancial

Phillip

Abraham

256 Jubilee Terrace

12

17-Nov-10

10-0011

Unfinancial

Michael

Philips

74 Ryder Road

13

14

15

16

Last Name

First Name

2017-18

17

Brown

Milton

26-Jul-17

18

Green

Ben

05-Jul-17

19

Green

Ross

14-Sep-17

20

Jones

Garry

01-Jan-00

21

Wells

John

11-Sep-17

22

Zoran

Christine

01-Jan-00

23

Sheet1

oldbrewer · Nov 17, 2017

[TABLE="width: 1760"]
<colgroup><col><col span="2"><col><col span="2"><col><col><col span="3"><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD]2010-11[/TD]
[TD]2011-12[/TD]
[TD]2012-2013[/TD]
[TD]2013-14[/TD]
[TD]2014-15[/TD]
[TD]2015-16[/TD]
[TD]2016-17[/TD]
[TD]2017-18[/TD]
[TD]Receipt No.[/TD]
[TD]Member Since[/TD]
[TD]Membership #[/TD]
[TD]M'ship Type[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD="colspan: 3"]Address - Street address[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29-Nov-10[/TD]
[TD="align: right"]06-Jul-11[/TD]
[TD="align: right"]02-Jul-12[/TD]
[TD="align: right"]14-Oct-13[/TD]
[TD="align: right"]26-Jul-14[/TD]
[TD="align: right"]30-Jun-15[/TD]
[TD="align: right"]19-Jul-16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29-Nov-10[/TD]
[TD]10-0001[/TD]
[TD]Unfinancial[/TD]
[TD]Bruce[/TD]
[TD]Smith[/TD]
[TD="colspan: 2"]53 Smith Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24-Nov-10[/TD]
[TD="align: right"]19-Jun-11[/TD]
[TD="align: right"]13-Jun-12[/TD]
[TD="align: right"]12-Jul-13[/TD]
[TD="align: right"]01-Jul-14[/TD]
[TD="align: right"]10-Aug-15[/TD]
[TD="align: right"]11-Jul-16[/TD]
[TD="align: right"]26-Jul-17[/TD]
[TD]N46[/TD]
[TD="align: right"]24-Nov-10[/TD]
[TD]10-0002[/TD]
[TD]Financial[/TD]
[TD]Milton[/TD]
[TD]Brown[/TD]
[TD="colspan: 2"]42 Yellow Parade[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29-Nov-10[/TD]
[TD="align: right"]17-Jun-11[/TD]
[TD="align: right"]10-Jun-12[/TD]
[TD="align: right"]13-Aug-13[/TD]
[TD="align: right"]02-Jul-14[/TD]
[TD="align: right"]15-Jul-15[/TD]
[TD="align: right"]01-Jan-00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29-Nov-10[/TD]
[TD]10-0003[/TD]
[TD]Life Member[/TD]
[TD]Garry[/TD]
[TD]Jones[/TD]
[TD="colspan: 2"]2 Green Court[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29-Nov-10[/TD]
[TD="align: right"]27-Jul-11[/TD]
[TD="align: right"]13-Jun-12[/TD]
[TD="align: right"]19-Jul-13[/TD]
[TD="align: right"]15-Sep-14[/TD]
[TD="align: right"]14-Aug-15[/TD]
[TD="align: right"]03-Aug-16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29-Nov-10[/TD]
[TD]10-0004[/TD]
[TD]Unfinancial[/TD]
[TD]Judy[/TD]
[TD]Green[/TD]
[TD="colspan: 2"]14 Jones Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov-10[/TD]
[TD="align: right"]06-Jul-11[/TD]
[TD="align: right"]13-Jun-12[/TD]
[TD="align: right"]03-Jul-13[/TD]
[TD="align: right"]01-Sep-14[/TD]
[TD="align: right"]30-Jun-15[/TD]
[TD="align: right"]18-Jan-17[/TD]
[TD="align: right"]14-Sep-17[/TD]
[TD]N96[/TD]
[TD="align: right"]17-Nov-10[/TD]
[TD]10-0005[/TD]
[TD]Financial[/TD]
[TD]Ross[/TD]
[TD]Green[/TD]
[TD="colspan: 2"]14 Jones Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov-10[/TD]
[TD="align: right"]06-Jul-11[/TD]
[TD="align: right"]13-Jun-12[/TD]
[TD="align: right"]03-Jul-13[/TD]
[TD="align: right"]02-Jul-14[/TD]
[TD="align: right"]28-Jul-15[/TD]
[TD="align: right"]05-Oct-16[/TD]
[TD="align: right"]05-Jul-17[/TD]
[TD]L79[/TD]
[TD="align: right"]17-Nov-10[/TD]
[TD]10-0006[/TD]
[TD]Financial[/TD]
[TD]Ben[/TD]
[TD]Green[/TD]
[TD="colspan: 2"]2/33 Johns Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov-10[/TD]
[TD="align: right"]06-Jul-11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17-Nov-10[/TD]
[TD]10-0007[/TD]
[TD]Unfinancial[/TD]
[TD]Joseph[/TD]
[TD]Banks[/TD]
[TD="colspan: 2"]13 Good Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov-10[/TD]
[TD="align: right"]27-Jul-11[/TD]
[TD="align: right"]26-Jun-12[/TD]
[TD="align: right"]14-Aug-13[/TD]
[TD="align: right"]04-Jun-14[/TD]
[TD="align: right"]20-Aug-15[/TD]
[TD="align: right"]09-May-16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17-Nov-10[/TD]
[TD]10-0008[/TD]
[TD]Life Member[/TD]
[TD]Christine[/TD]
[TD]Zoran[/TD]
[TD="colspan: 2"]8 Yellow Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov-10[/TD]
[TD="align: right"]06-Jul-11[/TD]
[TD="align: right"]26-Jun-12[/TD]
[TD="align: right"]02-Jul-13[/TD]
[TD="align: right"]04-Jun-14[/TD]
[TD="align: right"]04-Jun-15[/TD]
[TD="align: right"]01-Aug-16[/TD]
[TD="align: right"]11-Sep-17[/TD]
[TD]N93 (joint)[/TD]
[TD="align: right"]17-Nov-10[/TD]
[TD]10-0009[/TD]
[TD]Financial[/TD]
[TD]John[/TD]
[TD]Wells[/TD]
[TD="colspan: 2"]358 Milford Road[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov-10[/TD]
[TD="align: right"]05-Oct-11[/TD]
[TD="align: right"]11-Jul-12[/TD]
[TD="align: right"]08-Jul-13[/TD]
[TD]one[/TD]
[TD="align: right"]03-Jun-15[/TD]
[TD="align: right"]28-Jun-16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17-Nov-10[/TD]
[TD]10-0010[/TD]
[TD]Unfinancial[/TD]
[TD]Phillip[/TD]
[TD]Abraham[/TD]
[TD="colspan: 2"]256 Jubilee Terrace[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17-Nov-10[/TD]
[TD]10-0011[/TD]
[TD]Unfinancial[/TD]
[TD]Michael[/TD]
[TD]Philips[/TD]
[TD="colspan: 2"]74 Ryder Road[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]you have date errors in cols g and h[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]I made them in col H blank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Brown[/TD]
[TD]Milton[/TD]
[TD="align: right"]26/07/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jones[/TD]
[TD]Garry[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]this macro pulls data for the lower table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Green[/TD]
[TD]Ross[/TD]
[TD="align: right"]14/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Green[/TD]
[TD]Ben[/TD]
[TD="align: right"]05/07/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]you can add code for sorting by last name then first name, I am sure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Zoran[/TD]
[TD]Christine[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Wells[/TD]
[TD]John[/TD]
[TD="align: right"]11/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sub Macro4()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]' Macro4 Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]' Macro recorded 17/11/2017 by bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Dim fname(20), lname(20), myyear(20)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] rrow = 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For j = 2 To 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"] If Cells(j, 12) = "Financial" Or Cells(j, 12) = "Life Member" Then GoTo 20 Else GoTo 50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]20 Sum = Sum + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] fname(Sum) = Cells(j, 13)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] lname(Sum) = Cells(j, 14)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] If Cells(j, 8) = "" Then myyear(Sum) = " ": GoTo 50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] myyear(Sum) = Cells(j, 8)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50 Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For k = 1 To 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] tot = tot + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] rrow = rrow + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] If lname(tot) = "" Then GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(rrow, 2) = lname(tot)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(rrow, 3) = fname(tot)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(rrow, 4) = myyear(tot)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100 End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

mumps · Nov 17, 2017

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

wirra · Nov 17, 2017

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.

mumps · Nov 18, 2017

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

wirra · Nov 18, 2017

Thanks very much mumps. Works beautifully. Thanks for your time. It is very much appreciated.

mumps · Nov 19, 2017

You are very welcome.

Extract Data from One Worksheet to Another

wirra

New Member

Excel Facts

oldbrewer

Well-known Member

mumps

Well-known Member

wirra

New Member

mumps

Well-known Member

wirra

New Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

Extract Data from One Worksheet to Another

wirra

New Member

Excel Facts

oldbrewer

Well-known Member

mumps

Well-known Member

wirra

New Member

mumps

Well-known Member

wirra

New Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock