Loop thru in Excel I want to loop through below code, but I couldn\'t. I want to

Question

Loop thru in Excel

I want to loop through below code, but I couldn't.

I want to add 4 columns in each sheet between Start sheet and End sheet in this workbook.

I made a button in Master sheet.

what is wrong?

Thanks

Sub AddDateOnce()
Dim St As Long
Dim En As Long
Dim wsheet As Worksheet
Dim thisWB As Workbook
Dim act As Worksheet
Dim N As String, LaCol As Long
Dim Na As String
Set lkp = ThisWorkbook.Sheets("LookUp")
   Set wsheet = ThisWorkbook.ActiveSheet

Set thisWB = ActiveWorkbook
With thisWB

                       St = .Sheets("Start").Index
                    En = .Sheets("End").Index
                    For Each wsheet In thisWB.Worksheets
                       With wsheet
                        
                          
                            If .Index > St And .Index < En Then
                                   N = InputBox("Enter number ", "Do you want to Add?")
                                   If N <> "" Then
                                        lkp.Visible = xlSheetVisible 'because hidden
                                        For numtimes = 1 To N
                                                                       
                                                LaCol = wsheet.Cells(8, Columns.Count).End(xlToLeft).Column
                                               
                                                .Cells(7, LaCol).Resize(1, 4).EntireColumn.Insert shift:=xlToRight
                                                    lkp.Range("A1:D55").Copy Destination:=ThisWorkbook.Sheets(wsheet).Cells(7, LaCol)
                                            Next
                                     Else

                                            MsgBox "You did not enter a number"
                                       Exit Sub
                                  End If
                                 
                                lkp.Visible = xlSheetHidden
                           Else
                            End If
                            End With
                        Next
        
End With
End Sub

Explanation / Answer

..Since you want to process on sequential worksheets, the index number could be used in a For ... Next loop.,@

Sub AddDateOnce()
    Dim i As Long, w As Long, St As Long, En As Long, LaCol As Long
    Dim N As Variant
    With ActiveWorkbook
        St = .Sheets("Start").Index
        En = .Sheets("End").Index
        For w = (St + 1) To (En - 1)
            N = Application.InputBox(Prompt:="Enter number ", Default:=1, _
             Title:="Do you want to Add?", Type:=1)
            If CBool(N) Then
                With .Sheets(w)
                    For i = 1 To N
                        LaCol = .Cells(8, Columns.Count).End(xlToLeft).Column
                        .Cells(7, LaCol).Resize(1, 4).EntireColumn.Insert shift:=xlToRight
                        .Parent.Sheets("LookUp").Range("A1:D55").Copy _
                          Destination:=.Cells(7, LaCol)
                    Next i
                End With
            Else
                MsgBox "You did not enter a number. :("
            End If
        Next w
    End With
End Sub

I've used Application.InputBox rather than InputBox because this version allows you to specify the type and supply a default value; in this case, a number in both cases. I didn't unhide the LookUp worksheet as that is not necessary to copy from.

As mentioned previously, there is some confusion as to whether ThisWorkbook and ActiveWorkbook are the same thing. I've assumed that they are.

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.