Thread: Multi Worksheets get Columns to match
View Single Post
 
Old 10-27-2020, 07:50 AM
Purfleet Purfleet is offline Windows 10 Office 2019
Expert
  
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default
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....
Reply With Quote