Forum Discussion

oluwole's avatar
oluwole
Copper Contributor
May 22, 2020

error- invalid procedure call or argument

Sub Custodian_test() 'gets last used row Dim last_row As Long last_row = Worksheets("JANUARY 2019").Cells(Rows.Count, 1).End(xlUp).Row 'Debug.Print last_row 'gets last used column Dim last_colu...
Macros and VBA
bhushan_z's avatar
bhushan_z
Iron Contributor
May 25, 2020
2 questions:
1) What are you trying to achieve from this code?
2) Can u pls share the file? It seems to run without error at my end.
  • oluwole's avatar
    oluwole
    Copper Contributor
    May 28, 2020

    I have been able to resolve that using the do until loop. I have got another issue tho.

     

    Sub NsiaCopyAllData()

    'Dim TotalSheets As Long
    TotalSheets = Worksheets.Count
    'Debug.Print TotalSheets


    For i = 1 To TotalSheets

    If Worksheets(i).Name <> "ALL DATA" Then


    'Check Last used row on sheet to copy from
    clastrow = Worksheets(i).Cells.Find(what:="*", after:=Range("A1"), lookat:=xlPart, LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious, _
    MatchCase:=False).Row

    'Debug.Print CLastRow

    'Check last used column on copy sheet
    clastcolumn = Worksheets(i).Cells.Find(what:="*", after:=Range("A1"), lookat:=xlPart, LookIn:=xlValues, searchorder:=xlByColumns, searchdirection:=xlPrevious, _
    MatchCase:=False).Column

    'Debug.Print CLastColumn

    For j = 1 To clastrow

    Set tablerange = Range(Cells(1, 1), Cells(clastrow, clastcolumn))

    Worksheets(i).Activate
    Worksheets(i).Rows(tablerange).Select
    Selection.Copy

    Worksheets("ALL DATA").Activate

    On Error Resume Next

    'check last row in paste sheet
    Set PLastRow = Worksheets("ALL DATA").Cells.Find(what:="*", after:=Range("A1"), lookat:=xlPart, LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious, _
    MatchCase:=False).Row

    On Error GoTo 0

    Worksheets("ALL DATA").Cells(PLastRow + 2, 1).Select
    ActiveSheet.Paste

    Next

    End If


    Next


    End Sub

     

     

    - I am trying to copy data from different worksheets to one worksheet ("ALL DATA"), in the same workbook. All worksheets have different data range. 

    - I defined two variables to hold last column and last row. 

    - I get an error at the line where I select the range of data to be copied -"Worksheets(i).Rows(tablerange).Select". 

     

     

    Please help 

     

    bhushan_z 

Resources