replacing name with a variable

Hello.
Still very new to VBA so if this lot appears to be the ramblings of an idiot please humour me

Below is my spreadsheet(on sheet 1 of my workbook) with the following fields.ID, author,book title,publisher,year published,genre and copies sold.I turned the sheet into a table.Then on sheet 2 of my workbook,in Column A I placed 3 names of the authors from the spreadsheet.
Then I created a macro to go to sheet 2 and choose the name of an author.I did this by clicking on the authors field in the table,then selecting text filters option and pasting the name of the author in the Equals box in the custom autofilter.Then the macro would copy the name,filter the data and paste it onto sheet 3.
The problem arose when I changed the names of the authors that I wanted to select in sheet 2.Instead of getting the data of the new aithor it would go back to the original author.This is logical because that is what the original macro did.
So I have been looking for a way to replace the authors name in the macro code with a variable.Here is the code as it stands:

Please Login or Register  to view this content.

and next is the code I replaced it with.I created a variable called sun,and then allocated this to the address of the author in column A in sheet 2.I then replaced the name of the author with the variable sun in the actual VBA code.However it's not working and I think I may be doing something wrong.Then it occurred to me that this might not be at all possible within the realms of VBA I thought I would put it up here and see if any one has any suggestions.Thankyou

Please Login or Register  to view this content.

below is a copy of the sheet:

ID author book title publisher yearpublished genre copiessold
75 arnold laing laughter blue parrott 2014 adventure 789000
74 arnold laing smile blue parrot 2014 adventure 657000
76 arnold laing mr softy zoot 2015 thriller 89000
77 arnold laing mrs softy zoot 2015 thriller 671000
78 arnold laing gadabout yonkers 2016 thriller 345000
79 arnold laing superfly yonkers 2017 thriller 780000
80 arnold laing daft yonkers 2013 romance 890000
81 arnold laing silly yonkers 2013 romance 967000
73 arnold laing beaty zoot 2014 romance 450000
12 arthur smith which one blue parrot 2016 crime 180000
20 arthur smith the life of eddy bluesocks blue parrot 2015 biography 98000
19 arthur smith heart of the matter zoot 2013 biography 178000
18 arthur smith goldmine blue parrot 2014 crime 225000
17 arthur smith the beach blue parrot 2016 adventure 187000
16 arthur smith out of time zoot 2017 adventure 236000
15 arthur smith watershed zoot 2013 adventur 134000
13 arthur smith believe blue parrot 2015 crime 230000
11 arthur smith angel blue parrot 2016 crime 249000
14 arthur smith taxi to nowhere blue parrot 2017 crime 380000
100 carol reed light on the horizon yonkers 2013 thriller 986000
94 carol reed shifty yonkers 2014 thriller 67000
95 carol reed the big job yonkers 2014 thriller 24000
96 carol reed tell no lies yonkers 2015 thriller 479000
97 carol reed once blue parrot 2016 romance 456000
98 carol reed spice blue parrot 2017 adventure 789000
99 carol reed if only yonkers 2013 biography 231000
88 cathy ellis the years yonkers 2016 thriller 349000
91 cathy ellis jingles blue parrot 2013 adventure 670000
89 cathy ellis sad eyes blue parrot 2017 romance 987000
82 cathy ellis history yonkers 2014 romance 890000
87 cathy ellis in the wind yonkers 2016 romance 789000
86 cathy ellis autumn blue parrot 2015 romance 999000
84 cathy ellis winter blue parrot 2015 romance 780000
92 cathy ellis the grand zoot 2013 biography 123000
83 cathy ellis summer blue parrot 2014 romance 987000
93 cathy ellis romance and mr norman blue parrot 2013 romance 987000
90 cathy ellis talk to the wind blue parrot 2017 romance 967000
85 cathy ellis spring blue aprrot 2015 romance 997000
59 colin wilson head for heights zoot 2017 adventure 992000
56 colin wilson dapper blue parrot 2014 adventure 67000
58 colin wilson pirate blue parrot 2015 adventure 890000
60 colin wilson the calling zoot 2017 thriller 567000
61 colin wilson gadget man yonkers 2013 thriller 654000
62 colin wilson in from the cold yonkers 2013 thriller 780000
57 colin wilson shark blue parrot 2014 adventure 457000
51 gilbert riley sound bite blue parrot 2013 thriller 976000
43 gilbert riley mr jones investigates yonkers 2014 crime 178000
44 gilbert riley mr jones and the ghost yonkers 2014 crime 278000
45 gilbert riley mr jones in the dock yonkers 2017 crime 679000
46 gilbert riley mr jones and the bank job yonkers 2016 crime 456000
48 gilbert riley the return of mr jones yonkers 2013 crime 679000
47 gilbert riley goodbye mr jones yonkers 2013 crime 811000
49 gilbert riley the life of rome yonkers 2017 biography 211000
50 gilbert riley one amongst many blue parrot 2014 thriller 234000
52 gilbert riley you and me blue parrot 2013 romance 154000
53 gilbert riley fancy that zoot 2017 thriller 167000
54 gilbert riley give and take zoot 2015 romance 987000
55 gilbert riley showtime zoot 2016 adventure 45000
30 gill rafferty sign of the times zoot 2013 romance 980000
29 gill rafferty goblin blue parrot 2014 romance 456000
28 gill rafferty ghost blue parrot 2016 crime 450000
27 gill rafferty the sea yonkers 2013 romance 890000
26 gill rafferty rhodes yonkers 2015 crime 789000
31 gill rafferty mindfull yonkers 2014 crime 378000
22 gill rafferty sign of the times yonkers 2017 thriller 679000
25 gill rafferty evening yonkers 2015 romance 890000
21 gill rafferty old town yonkers 2016 thriller 178000
24 gill rafferty clouds blue parrot 2013 romance 189000
23 gill rafferty the rockstar yonkers 2016 thriller 670000
32 gill rafferty minds eye yonkers 2017 crime 890000
66 james green mask blue parrot 2016 romance 789000
72 james green the net zoot 2013 adventure 976000
71 james green the abbot blue parrot 2013 biography 123000
70 james green fishy zoot 2013 thriller 890000
69 james green tides zoot 2017 crime 378000
67 james green blue skies blue aprrot 2016 romance 907000
65 james green solitiude yonkers 2015 crime 780000
64 james green paradise yonkers 2014 crime 989000
63 james green flame yonkers 2014 crime 560000
68 james green lonely blue parrot 2017 romance 983000
10 ken davies switch zoot 2015 adventure 180000
2 ken davies dawn watch zoot 2013 adventure 260000
3 ken davies zero hour zoot 2015 adventure 279000
4 ken davies mr evans zoot 2013 crime 155000
1 ken davies xpatrol zoot 2017 adventure 175000
5 ken davies revenge blue parrot 2015 crime 225000
6 ken davies backdrop zoot 2003 crime 175000
7 ken davies firefly zoot 2014 crime 640000
8 ken davies newstime zoot 2014 adventure 450000
9 ken davies the department zoot 2014 crime 275000
34 molly brown escape blue parrot 2016 romance 234000
42 molly brown the ship blue parrot 2013 thriller 69000
35 molly brown forever yonkers 2015 romance 980000
36 molly brown serach for mr wrong zoot 2014 biography 478000
37 molly brown wonderful zoot 2013 thriller 878000
38 molly brown gilbert zoot 2013 biography 455000
39 molly brown shoulder to cry on yonkers 2015 romance 671000
40 molly brown jonah yonkers 2017 biography 689000
41 molly brown candid yonkers 2017 romance 790000
33 molly brown loves lost blue parrot 2017 romance 345000

An chance you could post the workbook?
Far easier to help amend the code when we can see the data layout properly.

BSB

Hi Bakerman,
many thanks for your reply but how would I refer to the combo box in the code

regards
bob