CODEWITHSUNDEEP
excelvba
user3232490
user3232490

Reputation: 13

Capture sheet name for a copy range

I have written vba to combine 50 sheets to one master sheet. It works. Now though they need to know what sheet the entry came from for correction purposes. How would I capture the sheet name?

For Each sh In ActiveWorkbook.Worksheets 

If sh.Name <> DestSh.Name 
Then Last = Range("A100000").End(xlUp).Row 
Set CopyRng = sh.Range("A2:k2" & Cells(Rows.Count, "A").End(xlUp).Row) 
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then 
MsgBox "There are not enough rows in the " & _ 
           "summary worksheet to place the data." 
GoTo ExitTheSub 
End If 

CopyRng.Copy 

With DestSh.Cells(Last + 1, "A") 
.PasteSpecial Paste:=xlPasteValues, skipBlanks:=True 
Application.CutCopyMode = False 
End With 

(CopyRng.Rows.Count).Value = sh.Name 

End If 

Next

Upvotes: 0

Views: 568

Answers (2)

user3232490
user3232490

Reputation: 13

This was the answer that did it... 

CopyRng.Copy

With DestSh.Cells(Last + 1, "A")
.PasteSpecial Paste:=xlPasteValues, skipBlanks:=True
Application.CutCopyMode = False
End With

DestSh.Cells(Last + 1, "Q").Resize(CopyRng.Rows.Count).Value = sh.Name

Thanks for the help guys.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

If you have a Range and want to know the Sheet associated with that Range :

Public Function WhichSheet(r As Range) As String
    WhichSheet = ""
    If Not r Is Nothing Then
        WhichSheet = r.Parent.Name
    End If
End Function

Upvotes: 1

Related Questions