VBA to insert data to specific sheets

[Jmoz092]

I'm having trouble with a code. I want to insert data into some cells on certain worksheets in my workbook. I can do this for one sheet, but it is not doing it for every sheet that I intend it to. Also, I don't know how to VBA a border around each cell and auto adjust the width of the column to fit the newly inserted text. Any help would be appreciated.

Sub AddCheckData()
    
    Dim ws As Worksheet
    Dim check(2) As String
            check(0) = "Yes"
            check(1) = "No"


   Application.ScreenUpdating = False
   
   For Each ws In ThisWorkbook.Worksheets
   If ws.Name Like "**-**-** Invoice" Then
   Range("F25").Value = ("Check Received?")
   Range("G25").Value = ("Check #")
   Range("H25").Value = ("Date Received")
   End If
        With Range("F26").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=Join(check, ",")
    End With
    Next ws
    Application.ScreenUpdating = True


End Sub

 

[dmt32]

Hi,
Your ranges are unqualified so your code will only apply to the activesheet

Update your code where shown in RED

Sub AddCheckData()
    
    Dim ws As Worksheet
    Dim check(2) As String
    check(0) = "Yes"
    check(1) = "No"
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "**-**-** Invoice" Then
            ws.Range("F25").Value = ("Check Received?")
            ws.Range("G25").Value = ("Check #")
            ws.Range("H25").Value = ("Date Received")
         
        With ws.Range("F26").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=Join(check, ",")
        End With
       End If
     Next ws
     Application.ScreenUpdating = True
End Sub

With regard to the cell formatting - turn the macro recorder on & perform the required format - this will give you the basic code needed which you can clean-up & call from your procedure when required.

Hope Helpful

Dave

 

[Jmoz092]

Dave, thanks for the help and suggestion. I was able to get what I was after with this:

Sub AddCheckData()
    
    Dim ws As Worksheet
    Dim check(2) As String
            check(0) = "Yes"
            check(1) = "No"


   Application.ScreenUpdating = False
   
   For Each ws In ThisWorkbook.Worksheets
   If ws.Name Like "**-**-** Invoice" Then
        ws.Range("F25").Value = ("Check Received?")
        ws.Range("G25").Value = ("Check #")
        ws.Range("H25").Value = ("Date Received")
   End If
        With ws.Range("F26").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=Join(check, ",")
    End With
    
    With ws.Range("F25:H26").Borders(xlDiagonalDown).LineStyle = xlNone
    End With
    With ws.Range("F25:H26").Borders(xlDiagonalUp).LineStyle = xlNone
    End With
    
    With ws.Range("F25:H26").Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With ws.Range("F25:H26").Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("F25:H26").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("F25:H26").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("F25:H26").Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("F25:H26").Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    
    With ws.Columns("F:H").EntireColumn.AutoFit
    End With
    
    Next ws
    
    Application.ScreenUpdating = True


End Sub

 

[dmt32]

Hi,
glad suggestions helped.
Appreciate your feedback

Dave