VBA Selecting specific sheets then copypaste specific cell

Question

I am trying to write a macro that loops through the pages within a workbook, and anything that contains DR the name of the sheet should be put to sheet Macro and a content of a cell.

Sub FnGetSheetsName()
    Dim mainworkBook As Workbook

    Set mainworkBook = ActiveWorkbook

    For i = 1 To mainworkBook.Sheets.Count
        If mainworkBook.Sheets.Name.Contains("DR") Then
            mainworkBook.Sheets("Macro").Range("A" & i) = mainworkBook.Sheets.Name
            mainworkBook.Sheets("Macro").Range("B" & i) = mainworkBook.Sheets.Range("B17")
        End If
    Next i
End Sub

It stops at: If mainworkBook.Sheets.Name.Contains("DR") Then

Thanks for your answers! :) Tibor

score 0 · Answer 1 · answered May 05 '17 at 08:06

You could try the Like method:

If mainworkBook.Sheets.Name Like "*DR*" Then instead of the line that causes problems.

Another option would be:

If InStr(1, mainworkBook.Sheets.Name, "DR", vbTextCompare) <> 0 Then

InStr will return the position that the text has been found or zero if it's not found.

score 0 · Answer 2 · edited May 23 '17 at 11:47

Watch out, in your code, you're writing this :

mainworkBook.Sheets.Name.Contains ...

Or this :

mainworkBook.Sheets.Range("B17")

Nevertheless, mainworkBook.Sheets is a variable that contains all your sheets. In order to test the name of just one sheet, you should write :

mainworkBook.Sheets(Integer).Name.Contains ...

I didn't know about the "Contains" function, so I used InStr

For i = 1 To mainworkBook.Sheets.Count
    If InStr(mainworkBook.Sheets(i).Name, "DR") <> 0 Then
        mainworkBook.Sheets("Macro").Range("A" & i) = mainworkBook.Sheets(i).Name
        mainworkBook.Sheets("Macro").Range("B" & i) = mainworkBook.Sheets(i).Range("B17")
    End If
Next i

score 0 · Answer 3 · edited May 05 '17 at 09:33

0

try this code:

Sub nnh()
    Dim x As Integer
    x = 0

    For Each Sheet In Worksheets
        If Sheet.Name Like "DB*" Then 
            x = x + 1
            Sheets("Macro").Range("A" & x) = Sheet.Name
            Sheets("Macro").Range("B" & x) = Sheets(Sheet.Name).Range("B17")
        End If
    Next Sheet
End Sub

edited May 05 '17 at 09:33

Pᴇʜ

56,719
10
49
73

answered May 05 '17 at 08:30

GGSandro

27
1
1
5

Because the number of sheets within a workbook is unlimited ([only limited by available memory](https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3)) you should dim `x` as `Long` instead of `Integer`. Always use `Long` instead of `Interger` **unless** you need to Interop with an old API call that expects a 16 bit int. Read more about [Integer and Long?](http://stackoverflow.com/a/26409520/3219613). – Pᴇʜ May 05 '17 at 09:42

VBA Selecting specific sheets then copypaste specific cell

3 Answers3