error- invalid procedure call or argument

Copper Contributor

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?

2 Replies
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.

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