Excel Macro gurus - Need help with multiple Sheets

647 Views | 3 Replies | Last: 9 yr ago by Summer95
Summer95
How long do you want to ignore this user?
AG
I want to use the FillAcrossSheets method to copy a range of cells to multiple sheets. The example I have found looks like this:

x = Array("Sheet1", "Sheet5", "Sheet7")
Sheets(x).FillAcrossSheets _
Worksheets("Sheet1").Range("A1:C5")

But I want to do define "x" by using a range of sheet index numbers instead of a list of sheet names. (The sheet names are very long). I am VERY new to this so please be gentle.

Do I have to define "x" as an array? Is there a way to define "x" as 38-49 where 38-49 are the sheet Index numbers?
texagbeliever
How long do you want to ignore this user?
I Believe you can just use the index numbers. I would still make x an array.

x = [38,39,...,49]
Then the rest should work out.

I think that it is poor coding practice to use index numbers instead of names because the user could accidentally slide a sheet over and totally screw up the code. Just a heads up there.
Summer95
How long do you want to ignore this user?
AG
I agree - but this is a one time operation in a one workbook with 52 sheets. I am changing some of the formatting for a time sheet workbook that has a different sheet for each week and each sheet is named for the end day of each week (September 20, 2014). I just want to be able to sort by job number & whoever created the stupid thing used too many merged cells. Once I fix the rest of this year, I won't have to do it again until next year.

thanks is for the help!
12thAngryMan
How long do you want to ignore this user?
AG
If they all are formatted the same and you want to change them all in the same way, why not just highlight all sheets and make the changes to one? It should apply them to the entire selection. Maybe I am misunderstanding your situation though.
Summer95
How long do you want to ignore this user?
AG
12thAngryMan - Thank you!!!!! I had no idea you could do that! VERY helpful thing to know.
Blue star for you!
Refresh
Page 1 of 1
 
×
subscribe Verify your student status
See Subscription Benefits
Trial only available to users who have never subscribed or participated in a previous trial.