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_column As Integer

'get row location to paste a value
Dim row_location As Range

'variable to sear for row_location
Dim row_loc_val As Long
row_loc_val = 1

'Variable to hold value in column A
Dim copy_details As Variant


'go through column A
For i = 2 To last_row

'get value in column A
copy_details = Worksheets("JANUARY 2019").Cells(i, 1).Value

'gets last used column
last_column = Worksheets("JANUARY 2019").Cells(last_row, Columns.Count).End(xlToLeft).Column
'Debug.Print last_column

'find row location where cell A = 1
Set row_location = Worksheets("JANUARY 2019").Cells(i, 1).Find(What:=row_loc_val, LookIn:=xlValues)


If InStr(UCase(copy_details), UCase("company no")) <> 0 Then
' Worksheets("JANUARY 2019").Cells(copy_details).Copy
Worksheets("JANUARY 2019").Cells(row_location).Offset(0, last_column + 1).Values = Worksheets("JANUARY 2019").Cells(copy_details).Values

'Debug.Print copy_details

 

End If

Next i


End Sub

 

I have written these lines of code but I keep getting the error - invalid procedure call or argument at the highlighted section. What am I doing wrong and how do I correct this?

Macros and VBA

2 Replies

  • 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