Thread: Macro loop for Worksheets with same # columns but different # rows

Hi,

I have different workbooks with some worksheets on each. In each worksheet there are 3 columns with data, but not each worksheet have the same number of rows.
I did a loop where I want it to 1) add titles to columns 2) create new columns doing operations with the previous ones.
But I have a problem: as not all the worksheets have the same longitude the loop doesn’t work well on the long ones. How can I do to perform the same loop to all of them??
I attach the Code and an example. I use Excel 2007.

Thanks in advance,
RaquelC

Sub WorksheetLoop()
    Dim WS_Count As Integer
    Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
    For I = 1 To WS_Count
' inserts a title row at the begining of the 3 columns
        Sheets(I).Select
        ActiveWindow.SmallScroll Down:=-3
        Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "t mes (s)"
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "I (A)"
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "V (V)"
' Add title to new colums D where time is initialized
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "t (s)"
        Range("D2").Select
        ActiveCell.FormulaR1C1 = "0"
        Range("D3").Select
        ActiveCell.FormulaR1C1 = "=RC[-3]-R[-1]C[-3]+R[-1]C"
        Range("D3").Select
        Selection.AutoFill Destination:=Range("D3:D401")
        'Range("D3:D401").Select
' Set column D to format 0.00 (2 significant numbers)
        Range("D2:D401").Select
        Selection.NumberFormat = "0.00"
        Range("E1").Select
' Add title to new colums E where intensity is set to nA
        ActiveCell.FormulaR1C1 = "I (nA)"
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "=RC[-3]*1000000000"
        Range("E2").Select
        Selection.AutoFill Destination:=Range("E2:E401")
        Range("E2:E401").Select
        Selection.NumberFormat = "0.00"
   Next I
End Sub

Try this

Sub WorksheetLoop()

    Dim WS_Count As Integer
    Dim I As Integer
    Dim lLR As Long
            
' Set WS_Count equal to the number of worksheets in the active workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
    ' inserts a title row at the begining of the 3 columns
            
    With Sheets(I)
        lLR = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("A1").FormulaR1C1 = "t mes (s)"
        .Range("B1").FormulaR1C1 = "I (A)"
        .Range("C1").FormulaR1C1 = "V (V)"
    ' Add title to new colums D where time is initialized
        .Range("D1").FormulaR1C1 = "t (s)"
        .Range("D2").FormulaR1C1 = "0"
        .Range("D3").FormulaR1C1 = "=RC[-3]-R[-1]C[-3]+R[-1]C"
        .Range("D3").AutoFill Destination:=.Range("D3:D" & lLR)
    ' Set column D to format 0.00 (2 significant numbers)
        .Range("D2:D" & lLR).NumberFormat = "0.00"
    ' Add title to new colums E where intensity is set to nA
        .Range("E1").FormulaR1C1 = "I (nA)"
        .Range("E2").FormulaR1C1 = "=RC[-3]*1000000000"
        .Range("E2").AutoFill Destination:=.Range("E2:E" & lLR)
    ' Set column E to format 0.00 (2 significant numbers)
        .Range("E2:E" & lLR).NumberFormat = "0.00"
    End With
Next 'I

End Sub

Regards

Hi,
Give this a go,

Sub stuff()
    
    Dim LastCell As Long, sht As Worksheet

    For Each sht In Sheets
        sht.Select
        Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1") = "t mes (s)"
        Range("B1") = "I (A)"
        Range("C1") = "V (V)"
        Range("D1") = "t (s)"
        Range("E1") = "I (nA)"
        Range("D2") = 0
        Range("E2") = "=RC[-3]*1000000000"
        Range("E2").NumberFormat = "0.00"
        Range("D3") = "=RC[-3]-R[-1]C[-3]+R[-1]C"
        Range("D3").NumberFormat = "0.00"

        LastCell = Cells(Rows.Count, "C").End(xlUp).Row

        Range("D3").AutoFill Destination:=Range(Cells(3, 4), Cells(LastCell, 4))
        Range("E2").AutoFill Destination:=Range(Cells(2, 5), Cells(LastCell, 5))
    Next sht

End Sub