Greetings! Tab name insert help

[ritagail]

Hi All -
I am creating a macro that loops through a file and reformats each worksheet then cuts and pastes the reformatted data into a Master sheet in another file. Since each file being reformatted has mulitple worksheets with differing tab names and file names I would like to insert the file name into cell b2 and the worksheet tab name into c2 so the data source is identifiable in the Master sheet. I cannot make this work. Any help would be greatly appreciated!

 

[andiemac]

Assuming you already have code that can activate the different worksheets in your workbooks, you might try:

Sub NamesAsVariables()
Dim sFileName, sTabName

'when the source file and tab are active while you are preparing to copy it
sFileName = ActiveWorkbook.Name
sTabName = ActiveWorkbook.ActiveSheet.Name

'after you have insterted it into your Master
Range("B2").Value = sFileName
Range("C2").Value = sTabName

End Sub