Here's a tough one for you all. What I am trying to do is create a macro so
I don't have to go in each of 28 different sheets & click each one of the 20
cells 1 by 1 & then click back to the corresponding "master" sheet cell to
reference it. I need each cell in the 28 sheets to reference to a different
column & row in the "master" sheet. Example: I click on "Sheet 1" & type
"='Master'!A1" I would have to do this 20 times for Sheet 1 & 20 times for
each of the 27 following sheets. I don't want to do this, so I figured I
could create a macro that would do this for me. I created a macro for the
first sheet & thought I could do a find & replace in the macro to change the
referenced column in the following 27 sheets to the next column in the
"Master" sheet, but don't know how to get that done. If that makes sense,
please help. This is a time sensitive project. Thanks for all your help.

Hi

Post the macro you have got so far with a description of what you need
to change.

Regards,
Per

On 19 Dec., 14:44, soldier1981
wrote:
Here's a tough one for you all. *What I am trying to do is create a macro so
I don't have to go in each of 28 different sheets & click each one of the 20
cells 1 by 1 & then click back to the corresponding "master" sheet cell to
reference it. *I need each cell in the 28 sheets to reference to a different
column & row in the "master" sheet. *Example: *I click on "Sheet 1" & type
"='Master'!A1" *I would have to do this 20 times for Sheet 1 & 20 times for
each of the 27 following sheets. * *I don't want to do this, so I figured I
could create a macro that would do this for me. *I created a macro for the
first sheet & thought I could do a find & replace in the macro to change the
referenced column in the following 27 sheets to the next column in the
"Master" sheet, but don't know how to get that done. *If that makes sense,
please help. *This is a time sensitive project. *Thanks for all your help.

This is the macro I have so far. I need to move the columns to the right for
each of the next 27 worksheets. I have about 20 more cells that need
changing so this is only part of the macro. Thanks. ~J

Sub

ActiveCell.FormulaR1C1 = "='USR Master'!R[3]C[-16]"
Range("S6:AC9").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-16]"
Range("S10:AC13").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-3]C[-16]"
Range("AD2:CG10").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-27]"
Range("BH11:CG13").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-36]"
Range("BD35:BF37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-6]C[-53]"
Range("BK35:BM37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-5]C[-60]"
Range("BR35:BT37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-4]C[-67]"
Range("BD40:BF42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-7]C[-53]"
Range("BK40:BM42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-6]C[-60]"
Range("BR40:BT42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-5]C[-67]"
Range("BY20:CE22").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[19]C[-74]"
Range("BY23:CE25").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[16]C[-74]"
Range("CA32:DG33").Select

End Sub


"Per Jessen" wrote:

Hi

Post the macro you have got so far with a description of what you need
to change.

Regards,
Per

On 19 Dec., 14:44, soldier1981
wrote:
Here's a tough one for you all. What I am trying to do is create a macro so
I don't have to go in each of 28 different sheets & click each one of the 20
cells 1 by 1 & then click back to the corresponding "master" sheet cell to
reference it. I need each cell in the 28 sheets to reference to a different
column & row in the "master" sheet. Example: I click on "Sheet 1" & type
"='Master'!A1" I would have to do this 20 times for Sheet 1 & 20 times for
each of the 27 following sheets. I don't want to do this, so I figured I
could create a macro that would do this for me. I created a macro for the
first sheet & thought I could do a find & replace in the macro to change the
referenced column in the following 27 sheets to the next column in the
"Master" sheet, but don't know how to get that done. If that makes sense,
please help. This is a time sensitive project. Thanks for all your help.

As per already sd, you need to be a bit more descriptive of what you
need. However, I tried something which you can look at to find if this
would be your solution.

Create a worksheet function which will give the name of a sheet by
index. Copy the code below and paste it in a standard code module.

Function SheetByIndex(ByRef Index As Long) As String
SheetByIndex = ActiveWorkbook.Sheets(Index).Name
End Function

Once you do this, type the formula below in one of your cell A1...
=SheetbyIndex(1)

This will give you the name of the 1st sheet. Now you can also use
indirect cell reference to get the content of a cell....
suppose you want the content of A1 in 1st sheet (suppose 1st sheet is
Sheet1)...

syntax for Address Function
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
where Row_num is the row number to use in the cell
reference.
Column_num is the column number to use in the cell
reference.
Abs_num specifies the type of reference to return.

Type the following formula in cell A2
= Indirect("Sheet1!"&$A$1)

The cell A2 will show the content of cell A1 in "Sheet1"... Rather
than typing out each and every reference, you can use the worksheet
function called "address"...
Type out the formula below in A3.

=ADDRESS(ROW()-2,1,1,1,$a$1)

The above formula will return string as "Sheet1!$A$1".

Based on all these three logic, you can show the contents of any and
all sheets of the workbook without much hardwork, and you will see
that you are not using any macro other than customer user defined
function to return sheet name as string...

You may have a look at an example workbook which can be downloaded
from the following link.

http://www.sockofiles.350.com/summaryinmaster.xls

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other Resources
Please visit: http://socko.wordpress.com

Sorry Socko, but that's ridiculously complicated for me to understand. I
figured there has to be an easier way to write the code. I just want to
change this code to move the column one to the right. Why can't it say ='USR
Master'!A1 or whatever cell I want chosen instead of R[-26]C[-84]???? That
just seems like it should be easier to write code using that format. So next
time I want to write the code, I could just write ='USR Master'!B2 instead of
having to write R[-26]C[-83]. Why can't it be that easy? Thanks for all the
input thus far.

ActiveCell.FormulaR1C1 = "='USR Master'!R[-26]C[-84]"
Range("CV71:CV76").Select

~J


"Socko" wrote:

As per already sd, you need to be a bit more descriptive of what you
need. However, I tried something which you can look at to find if this
would be your solution.

Create a worksheet function which will give the name of a sheet by
index. Copy the code below and paste it in a standard code module.

Function SheetByIndex(ByRef Index As Long) As String
SheetByIndex = ActiveWorkbook.Sheets(Index).Name
End Function

Once you do this, type the formula below in one of your cell A1...
=SheetbyIndex(1)

This will give you the name of the 1st sheet. Now you can also use
indirect cell reference to get the content of a cell....
suppose you want the content of A1 in 1st sheet (suppose 1st sheet is
Sheet1)...

syntax for Address Function
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
where Row_num is the row number to use in the cell
reference.
Column_num is the column number to use in the cell
reference.
Abs_num specifies the type of reference to return.

Type the following formula in cell A2
= Indirect("Sheet1!"&$A$1)

The cell A2 will show the content of cell A1 in "Sheet1"... Rather
than typing out each and every reference, you can use the worksheet
function called "address"...
Type out the formula below in A3.

=ADDRESS(ROW()-2,1,1,1,$a$1)

The above formula will return string as "Sheet1!$A$1".

Based on all these three logic, you can show the contents of any and
all sheets of the workbook without much hardwork, and you will see
that you are not using any macro other than customer user defined
function to return sheet name as string...

You may have a look at an example workbook which can be downloaded
from the following link.

http://www.sockofiles.350.com/summaryinmaster.xls

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other Resources
Please visit: http://socko.wordpress.com

You are selecting large ranges (for example, S6:AC9) and when doing
something with the ActiveCell (which would be S6 in the example selected
range) which is confusing me as to what you are actually trying to do. Can
you describe in (detailed) words what it is you are attempting to do? Then,
after you do that, give us one example (again, in words) giving us a range,
column or cell (depending on what you are trying to do), and tell us exactly
what you want to do with that range, column or cell.

--
Rick (MVP - Excel)


"soldier1981" wrote in message
...
This is the macro I have so far. I need to move the columns to the right
for
each of the next 27 worksheets. I have about 20 more cells that need
changing so this is only part of the macro. Thanks. ~J

Sub

ActiveCell.FormulaR1C1 = "='USR Master'!R[3]C[-16]"
Range("S6:AC9").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-16]"
Range("S10:AC13").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-3]C[-16]"
Range("AD2:CG10").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-27]"
Range("BH11:CG13").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-36]"
Range("BD35:BF37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-6]C[-53]"
Range("BK35:BM37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-5]C[-60]"
Range("BR35:BT37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-4]C[-67]"
Range("BD40:BF42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-7]C[-53]"
Range("BK40:BM42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-6]C[-60]"
Range("BR40:BT42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-5]C[-67]"
Range("BY20:CE22").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[19]C[-74]"
Range("BY23:CE25").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[16]C[-74]"
Range("CA32:DG33").Select

End Sub


"Per Jessen" wrote:

Hi

Post the macro you have got so far with a description of what you need
to change.

Regards,
Per

On 19 Dec., 14:44, soldier1981
wrote:
Here's a tough one for you all. What I am trying to do is create a
macro so
I don't have to go in each of 28 different sheets & click each one of
the 20
cells 1 by 1 & then click back to the corresponding "master" sheet cell
to
reference it. I need each cell in the 28 sheets to reference to a
different
column & row in the "master" sheet. Example: I click on "Sheet 1" &
type
"='Master'!A1" I would have to do this 20 times for Sheet 1 & 20 times
for
each of the 27 following sheets. I don't want to do this, so I
figured I
could create a macro that would do this for me. I created a macro for
the
first sheet & thought I could do a find & replace in the macro to
change the
referenced column in the following 27 sheets to the next column in the
"Master" sheet, but don't know how to get that done. If that makes
sense,
please help. This is a time sensitive project. Thanks for all your
help.

Okay I'll try to explain in detail.

I have 1 sheet named "Master" & 20 sheets named uniquely. In the "Master"
sheet are 20 columns, each 30 rows long containing numbers, which are used to
link to the 20 different sheets. The uniquely named sheets contain 112
columns each 15 pixels wide & 70 rows 15 pixels high. I did this so I could
create different sized boxes & such without having to make the column widths
different. I don't know why I did it this way, but I just did.

Each of these 20 sheets has 30 cells (most are merged with several other
cells making them bigger) where data is linked from the "Master" sheet.
These cells are spread all over the worksheet, but are in the same location
from sheet to sheet.

I've created the first sheet (Sheet1) with all the correct conditional
formats & referenced all the cells I need back to the "Master" sheet. When I
start a new sheet (Sheet2) I copy & paste all of Sheet1 to Sheet2 which
copies all my conditional formats & such. It also copies the references for
Sheet1, but I don't want that. I want to have different references in there.
The different references will be exactly 1 column to the right from the old
references in the "Master" sheet. Does that make sense?? So...when I copy &
paste Sheet1 into Sheet2, cell A1 looks like this...='Master'!A1.......But
what I want it to say is ='Master'!B1

Also if I create Sheet3 by copy & pasting from Sheet1, the formula in Sheet3
& cell A1 should read...='Master'!C1 I hope that makes sense.

Each unique sheet in the workbook will need to continue that pattern. What
I want to do is find a way I can keep creating new sheets & every time I do
the new sheet will reference the "Master" sheet, but one column to the right
of the previous sheet. I've created a macro...sort of...to help, but I don't
think it's very efficient. Please help!!! Thanks.

~J

"Rick Rothstein" wrote:

You are selecting large ranges (for example, S6:AC9) and when doing
something with the ActiveCell (which would be S6 in the example selected
range) which is confusing me as to what you are actually trying to do. Can
you describe in (detailed) words what it is you are attempting to do? Then,
after you do that, give us one example (again, in words) giving us a range,
column or cell (depending on what you are trying to do), and tell us exactly
what you want to do with that range, column or cell.

--
Rick (MVP - Excel)


"soldier1981" wrote in message
...
This is the macro I have so far. I need to move the columns to the right
for
each of the next 27 worksheets. I have about 20 more cells that need
changing so this is only part of the macro. Thanks. ~J

Sub

ActiveCell.FormulaR1C1 = "='USR Master'!R[3]C[-16]"
Range("S6:AC9").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-16]"
Range("S10:AC13").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-3]C[-16]"
Range("AD2:CG10").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-27]"
Range("BH11:CG13").Select
ActiveCell.FormulaR1C1 = "='USR Master'!RC[-36]"
Range("BD35:BF37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-6]C[-53]"
Range("BK35:BM37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-5]C[-60]"
Range("BR35:BT37").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-4]C[-67]"
Range("BD40:BF42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-7]C[-53]"
Range("BK40:BM42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-6]C[-60]"
Range("BR40:BT42").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[-5]C[-67]"
Range("BY20:CE22").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[19]C[-74]"
Range("BY23:CE25").Select
ActiveCell.FormulaR1C1 = "='USR Master'!R[16]C[-74]"
Range("CA32:DG33").Select

End Sub


"Per Jessen" wrote:

Hi

Post the macro you have got so far with a description of what you need
to change.

Regards,
Per

On 19 Dec., 14:44, soldier1981
wrote:
Here's a tough one for you all. What I am trying to do is create a
macro so
I don't have to go in each of 28 different sheets & click each one of
the 20
cells 1 by 1 & then click back to the corresponding "master" sheet cell
to
reference it. I need each cell in the 28 sheets to reference to a
different
column & row in the "master" sheet. Example: I click on "Sheet 1" &
type
"='Master'!A1" I would have to do this 20 times for Sheet 1 & 20 times
for
each of the 27 following sheets. I don't want to do this, so I
figured I
could create a macro that would do this for me. I created a macro for
the
first sheet & thought I could do a find & replace in the macro to
change the
referenced column in the following 27 sheets to the next column in the
"Master" sheet, but don't know how to get that done. If that makes
sense,
please help. This is a time sensitive project. Thanks for all your
help.