CODEWITHSUNDEEP
excelvbaextract
Mourst
Mourst

Reputation: 1

How to extract worksheet name in VBA?

I have a script that rips some data for me from a certain tab in multiple excel workbooks.

I am wondering how can I add to that to extract the workbook name for each workbook it goes through

This is what I am using:

Dim fPath As String
Dim iSheet As String
Dim oSheet As String
Dim BMsheet As String

Dim country, bas, play As String
Dim fileNames As Range
Dim file As Range
Dim oWorkbook As Excel.Workbook ' outlook workbook

Dim MyRange As Range

iSheet = "INPUT"
oSheet = "Data"
BMsheet = "Potential Discovery Phasing"

Dim fHandle As New FileSystemObject

ThisWorkbook.Worksheets(iSheet).Activate
Set fileNames = Range("files")
ThisWorkbook.Worksheets(oSheet).Activate
Range("start").Activate

On Error GoTo NotFound:

For Each file In fileNames.Cells
    If fHandle.FileExists(file.Value) Then
        Set oWorkbook = Workbooks.Open(file.Value, False, True)
        'extract columns

        ''''''''''''''''''''''''''''''''''''''''''''''''
        '
        ''''''''''''''''''''''''''''''''''''''''''''''''
        oWorkbook.Worksheets(BMsheet).Select

        If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

        Range("A6").Select

        ActiveCell.Offset(1, 0).Select
        Set MyRange = Range(ActiveCell, ActiveCell.Offset(32, 7))

        MyRange.Select

        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy

        ThisWorkbook.Activate
        ThisWorkbook.Worksheets(oSheet).Select
        Selection.PasteSpecial xlValues

       'While ActiveCell.Value <> ""
             'ActiveCell.Offset(0, -1).Value = file.Offset(0, -2).Value
             ActiveCell.Offset(33, 0).Activate
       'Wend

        Application.CutCopyMode = False
        oWorkbook.Close SaveChanges:=False

        ActiveCell.Select
        file.Offset(0, 1).Value = "Yes"
    Else
        file.Offset(0, 1).Value = "No"
    End If

Skip: Next file

    Exit Sub

NotFound:
    GoTo Skip
End Sub

I am fairly new to VBA so excuse my lack of knowledge

Cheers

Upvotes: 0

Views: 3018

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

dim sheet as worksheet
dim wb as workbook
set wb = thisworkbook

for each sheet in wb.Sheets
    debug.print; sheet.Name
next sheet

Because we're nerds and want to check things:

Private Sub this()
    For i = 0 To 99
        Debug.Print i;
        'prints to the same line
    Next i

    For i = 0 To 99
        Debug.Print ; i
        prints to next line
    Next i
End Sub

Upvotes: 1

Related Questions