Relative Sheet Reference [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,

I am trying to write a macro (new to VB) that shifts contents in a branch layout to be aligned on the left, column A. I have this code that works fine:
Sub ShiftValuesLeft()
Dim iRows As Integer, iCols As Integer, x As Integer, y As Integer, icount As Integer
Dim MyArray(), rTop As Range
iRows = Range("Top").CurrentRegion.Rows.Count
iCols = Range("Top").CurrentRegion.Columns.Count - 1
Set rTop = Range("Top")
ReDim MyArray(iRows, iCols)


For x = 1 To iRows
For y = 1 To iCols
If rTop.Cells(x, y) <> "" Then
MyArray(x, icount) = rTop.Cells(x, y)
icount = icount + 1
End If

Next y
icount = 0

Next x

For x = 1 To iRows
For y = 1 To iCols
If MyArray(x, icount) <> "" Then
Range("Output").Cells(x, y) = MyArray(x, icount)
icount = icount + 1
End If
Next y
icount = 0
Next x

End Sub

However, the workbook I am using has dozens of tabs and I want my macro to assign the same cell in each sheet (A5) as "Top", and a Go To Last Cell function (separated by a blank row and also moved over to column A), to name the "Output". Each sheet has a different number of rows and I want the macro to assign these names in reference to the active sheet, not the original sheet I put labels on. If someone can modify my existing code to fix all these issues I would be very grateful!

2 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
'use
for each sht in activeworkbook.worksheets.
next sht
'for switching through all your tabs

'to name the cell A5, use the following in the above loop
ActiveWorkbook.Names.Add Name:="top", RefersToR1C1:="='" & activesheet.name & "'!R5C1"
Guru, can you tell me where to insert these statements? If you could copy my original in your reply with your suggestions I think that will help me understand it better.
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
'to name the cell A5 in each worksheet as top
for each sht in activeworkbook.worksheets
sht.activate
ActiveWorkbook.Names.Add Name:="top", RefersToR1C1:="='" & activesheet.name & "'!R5C1"
next sht

Rest of your mail is not clear to understand.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!