0

I am trying to resize multiple columns in multiple sheets. For some reason only the first one works. Is it due to the fact that I am changing both variables?

    Set targetSh = owb.Sheets("INPUT_INSTRUMENT")

    Set resizeSh = owb.Sheets("Info")
    tablename = "Table5"
    Call RowsAction(targetSh, resizeSh, tablename)

    Set resizeSh = owb.Sheets("Review")
    tablename = "Table17"
    Call RowsAction(targetSh, resizeSh, tablename)


Sub RowsAction(ByRef targetSh As Worksheet, resizeSh As Worksheet, tablename As String)
    Dim i, iLastRow As Integer, oLastRow As ListRow, srcRow As Range

    Last = targetSh.Range("A1", targetSh.Cells(Rows.count, "A").End(xlUp)).count - 2

    For i = 1 To Last
        Set srcRow = resizeSh.ListObjects(tablename).ListRows(i).Range
        Set oLastRow = resizeSh.ListObjects(tablename).ListRows.Add
        srcRow.Copy
        oLastRow.Range.PasteSpecial
        Application.CutCopyMode = False
    Next
End Sub
shafac14
  • 15
  • 2
  • 7
  • I don't see a declaration for `Last`? Do you have `Option Explicit` at the top of the code module? – Cindy Meister Apr 11 '18 at 13:27
  • I don't see any resizing of columns, here. But do you really mean to be copying a row and pasting it at the bottom of the same table? Should the second line of the For loop be = targetSh not = resizeSh? – Cindy Meister Apr 11 '18 at 13:30
  • @CindyMeister Thank you for pointing me in that direction. I just changed iLastRow As Integer to Last As Integer and it works. Why option explcit? – shafac14 Apr 11 '18 at 13:51
  • 1
    @shafac14 Note that `Dim i, Last As Integer` only declares `Last` as `Integer` but `i` remains `Variant` you need to specify a type for **every** variable. Also Excel has more rows than `Integer` can handle, so you should always use `Long` instead of `Integer` ([there is no benefit in `Integer` at all in VBA](https://stackoverflow.com/a/26409520/3219613)): `Dim i As Long, Last As Long`. Use `Option Explicit` to force that every variable is declared. This prevents you from typos and many issues. – Pᴇʜ Apr 11 '18 at 14:00
  • 1
    Why Option Explicit: That will show an error message if you misspell a variable name or forget to declare one. So you might not have had to ask here, for example :-) – Cindy Meister Apr 11 '18 at 14:08

0 Answers0