Multi Worksheets get Columns to match

Purfleet · #1 10-27-2020, 07:50 AM

We would need to change parts, or to make it a variable

for example, this line has a varible for the number of rows (namescount + 4), so we would need to work out where the new 'J' would be.

Code:

Set SSOldData = Worksheets("SS Hours  Scoutbook Money").Range("a5:j" & NamesCount + 4)

If we can assume that the word 'Prizes will always be the last column in the table and it will always be in row 4 we can do

Code:

SSLastCol = Chr(64 + Worksheets("SS Hours  Scoutbook Money").Range("4:4").Find(what:="Prizes").Column)

Then the orginal row becomes

Code:

Set SSOldData = Worksheets("SS Hours  Scoutbook Money").Range("a5:" & SSLastCol  & NamesCount + 4)

Might need updating in 7 or 8 places, easy enough

Edit

and the totals columns....

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Comparing between 2 worksheets and retreiving data if a match	FUGMAN	Excel Programming	8	02-18-2017 09:47 AM
Counting only the unique names in 3 columns on separate worksheets.	Ryga38	Excel	1	04-15-2016 07:21 AM
Match Multi Cells	gdavey	Excel Programming	1	09-06-2015 11:09 AM
Index Match Function across different worksheets	shay_mt	Excel	2	04-27-2015 06:04 AM
Multi Level Subtotals w 2 Grand Totals That Don't Match	DavidW	Excel	3	10-09-2014 07:08 AM