Need a function to reference Sheet name

[noorie007]

I have developed a VBA macro that combines data from multiple sheets (Sheet1, Sheet2, Sheet3 etc) and creates a new sheet ("ConsolidatedData") that combines data from all sheets.

Then I have another Sheet ("Update Value") that performs a Vlookup on sheet ("ConsolidatedData") - This referencce is broken as I run the macro. Reason being the macro deletes an old "ConsolidatedData" sheet and adds a new "ConsolidatedData" sheet.

I understand why this is happening. I just need to update the lookup function so it refers to the sheet name i.e. "ConsolidatedData" anytime i calculate the Vlookup function.

Thanks!

[alansidman]

Maybe instead of deleting the old sheet which would then break the connection, why not have the code clear the contents of the sheet. In this manner, the sheet stays intact.

[noorie007]

I copied the macro from Microsoft help. I dont know how to modify it to do that. It is as follows:

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

' Find the last row with data on the summary worksheet.
Last = LastRow(DestSh)

' Specify the range to place the data.
Set CopyRng = sh.Range("A28:AI400")

' Test to see whether there are enough rows in the summary
' worksheet to copy all the data.
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the " & _
"consolidated data worksheet to place the data."
GoTo ExitTheSub
End If

[noorie007]

' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("ConsolidatedData").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Add a new summary worksheet.
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "ConsolidatedData"

[noorie007]

For Each sh In ThisWorkbook.Worksheets
Select Case UCase(sh.CodeName)
Case "SHEET13", "SHEET14", "SHEET15", "SHEET16", "SHEET17", "SHEET18", "SHEET19"


' Find the last row with data on the summary worksheet.
Last = LastRow(DestSh)

' Specify the range to place the data.
Set CopyRng = sh.Range("A28:AI400")

' Test to see whether there are enough rows in the summary
' worksheet to copy all the data.
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the " & _
"consolidated data worksheet to place the data."
GoTo ExitTheSub
End If

' This statement copies values and formats from each
' worksheet.
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

[alansidman]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[noorie007]

Please Login or Register  to view this content.

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function



Sub ConsolidateDataFromAllNodes()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("ConsolidatedData").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Add a new summary worksheet.
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "ConsolidatedData"

' Loop through all worksheets and copy the data to the
' summary worksheet.
'For Each sh In ThisWorkbook.Worksheets
' If sh.Name <> DestSh.Name Then

For Each sh In ThisWorkbook.Worksheets
Select Case UCase(sh.CodeName)
Case "SHEET13", "SHEET14", "SHEET15", "SHEET16", "SHEET17", "SHEET18", "SHEET19"


' Find the last row with data on the summary worksheet.
Last = LastRow(DestSh)

' Specify the range to place the data.
Set CopyRng = sh.Range("A28:AI400")

' Test to see whether there are enough rows in the summary
' worksheet to copy all the data.
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the " & _
"consolidated data worksheet to place the data."
GoTo ExitTheSub
End If

' This statement copies values and formats from each
' worksheet.
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

' Optional: This statement will copy the sheet
' name in the H column.
DestSh.Cells(Last + 1, "AK").Resize(CopyRng.Rows.Count).Value = sh.Name

End Select
'End If
Next sh

ExitTheSub:

Application.Goto DestSh.Cells(1)

' AutoFit the column width in the summary sheet.
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

[noorie007]

Is the above thread alright?