none
VBA code RRS feed

  • Question

  • 1.how to approch vba code for below requirement

    2.sheet1 having country names. once loop gone through counrty name value will be return in respective sheet name column "A" retun value is "1"

    OUTPUT FILE

    Thursday, August 6, 2015 3:39 AM

All replies

  • Try the following code. If the worksheet for the country name does not exist then it will add the worksheet.

    Sub TestLoop()
        Dim rngToProcess As Range
        Dim cel As Range
        Dim wsCtry As Worksheet
       
        With Worksheets("Sheet1")       'Edit "Sheet1" to name of sheet with list
            Set rngToProcess = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        End With
       
        For Each cel In rngToProcess
            Set wsCtry = Nothing
            On Error Resume Next
            Set wsCtry = Worksheets(cel.Value)
            On Error GoTo 0
            If wsCtry Is Nothing Then   'Will be nothing if sheet does not already exist
                'Add the worksheet if it does not already exist
                Set wsCtry = Sheets.Add(After:=Sheets(Sheets.Count))
                wsCtry.Name = cel.Value
            End If
            With wsCtry
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = 1
            End With
        Next cel
    End Sub


    Regards, OssieMac

    • Proposed as answer by André Santo Thursday, August 6, 2015 12:30 PM
    Thursday, August 6, 2015 6:58 AM
  • Maybe this is what you want?

    http://www.rondebruin.nl/win/s3/win006.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, August 7, 2015 1:23 AM