error- invalid procedure call or argument

%3CLINGO-SUB%20id%3D%22lingo-sub-1411203%22%20slang%3D%22en-US%22%3Eerror-%20invalid%20procedure%20call%20or%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411203%22%20slang%3D%22en-US%22%3E%3CP%3ESub%20Custodian_test()%3C%2FP%3E%3CP%3E'gets%20last%20used%20row%3CBR%20%2F%3EDim%20last_row%20As%20Long%3CBR%20%2F%3Elast_row%20%3D%20Worksheets(%22JANUARY%202019%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3C%2FP%3E%3CP%3E'Debug.Print%20last_row%3C%2FP%3E%3CP%3E'gets%20last%20used%20column%3CBR%20%2F%3EDim%20last_column%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3E'get%20row%20location%20to%20paste%20a%20value%3CBR%20%2F%3EDim%20row_location%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3E'variable%20to%20sear%20for%20row_location%3CBR%20%2F%3EDim%20row_loc_val%20As%20Long%3CBR%20%2F%3Erow_loc_val%20%3D%201%3CBR%20%2F%3E%3CBR%20%2F%3E'Variable%20to%20hold%20value%20in%20column%20A%3CBR%20%2F%3EDim%20copy_details%20As%20Variant%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'go%20through%20column%20A%3CBR%20%2F%3EFor%20i%20%3D%202%20To%20last_row%3C%2FP%3E%3CP%3E'get%20value%20in%20column%20A%3CBR%20%2F%3Ecopy_details%20%3D%20Worksheets(%22JANUARY%202019%22).Cells(i%2C%201).Value%3C%2FP%3E%3CP%3E'gets%20last%20used%20column%3CBR%20%2F%3Elast_column%20%3D%20Worksheets(%22JANUARY%202019%22).Cells(last_row%2C%20Columns.Count).End(xlToLeft).Column%3CBR%20%2F%3E'Debug.Print%20last_column%3CBR%20%2F%3E%3CBR%20%2F%3E'find%20row%20location%20where%20cell%20A%20%3D%201%3CBR%20%2F%3ESet%20row_location%20%3D%20Worksheets(%22JANUARY%202019%22).Cells(i%2C%201).Find(What%3A%3Drow_loc_val%2C%20LookIn%3A%3DxlValues)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIf%20InStr(UCase(copy_details)%2C%20UCase(%22company%20no%22))%20%26lt%3B%26gt%3B%200%20Then%3CBR%20%2F%3E'%20Worksheets(%22JANUARY%202019%22).Cells(copy_details).Copy%3CBR%20%2F%3E%3CFONT%20color%3D%22%23000000%22%3E%3CSTRONG%3EWorksheets(%22JANUARY%202019%22).Cells(row_location).Offset(0%2C%20last_column%20%2B%201).Values%20%3D%20Worksheets(%22JANUARY%202019%22).Cells(copy_details).Values%3C%2FSTRONG%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3E'Debug.Print%20copy_details%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3ENext%20i%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20these%20lines%20of%20code%20but%20I%20keep%20getting%20the%20error%20-%20invalid%20procedure%20call%20or%20argument%20at%20the%20highlighted%20section.%20What%20am%20I%20doing%20wrong%20and%20how%20do%20I%20correct%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1411203%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415485%22%20slang%3D%22en-US%22%3ERe%3A%20error-%20invalid%20procedure%20call%20or%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415485%22%20slang%3D%22en-US%22%3E2%20questions%3A%3CBR%20%2F%3E1)%20What%20are%20you%20trying%20to%20achieve%20from%20this%20code%3F%3CBR%20%2F%3E2)%20Can%20u%20pls%20share%20the%20file%3F%20It%20seems%20to%20run%20without%20error%20at%20my%20end.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423274%22%20slang%3D%22en-US%22%3ERe%3A%20error-%20invalid%20procedure%20call%20or%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423274%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20able%20to%20resolve%20that%20using%20the%20do%20until%20loop.%20I%20have%20got%20another%20issue%20tho.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20NsiaCopyAllData()%3C%2FP%3E%3CP%3E'Dim%20TotalSheets%20As%20Long%3CBR%20%2F%3ETotalSheets%20%3D%20Worksheets.Count%3CBR%20%2F%3E'Debug.Print%20TotalSheets%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFor%20i%20%3D%201%20To%20TotalSheets%3C%2FP%3E%3CP%3EIf%20Worksheets(i).Name%20%26lt%3B%26gt%3B%20%22ALL%20DATA%22%20Then%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'Check%20Last%20used%20row%20on%20sheet%20to%20copy%20from%3CBR%20%2F%3Eclastrow%20%3D%20Worksheets(i).Cells.Find(what%3A%3D%22*%22%2C%20after%3A%3DRange(%22A1%22)%2C%20lookat%3A%3DxlPart%2C%20LookIn%3A%3DxlValues%2C%20searchorder%3A%3DxlByRows%2C%20searchdirection%3A%3DxlPrevious%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse).Row%3CBR%20%2F%3E%3CBR%20%2F%3E'Debug.Print%20CLastRow%3C%2FP%3E%3CP%3E'Check%20last%20used%20column%20on%20copy%20sheet%3CBR%20%2F%3Eclastcolumn%20%3D%20Worksheets(i).Cells.Find(what%3A%3D%22*%22%2C%20after%3A%3DRange(%22A1%22)%2C%20lookat%3A%3DxlPart%2C%20LookIn%3A%3DxlValues%2C%20searchorder%3A%3DxlByColumns%2C%20searchdirection%3A%3DxlPrevious%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse).Column%3CBR%20%2F%3E%3CBR%20%2F%3E'Debug.Print%20CLastColumn%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20j%20%3D%201%20To%20clastrow%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20tablerange%20%3D%20Range(Cells(1%2C%201)%2C%20Cells(clastrow%2C%20clastcolumn))%3CBR%20%2F%3E%3CBR%20%2F%3EWorksheets(i).Activate%3CBR%20%2F%3EWorksheets(i).Rows(tablerange).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3E%3CBR%20%2F%3EWorksheets(%22ALL%20DATA%22).Activate%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3E%3CBR%20%2F%3E'check%20last%20row%20in%20paste%20sheet%3CBR%20%2F%3ESet%20PLastRow%20%3D%20Worksheets(%22ALL%20DATA%22).Cells.Find(what%3A%3D%22*%22%2C%20after%3A%3DRange(%22A1%22)%2C%20lookat%3A%3DxlPart%2C%20LookIn%3A%3DxlValues%2C%20searchorder%3A%3DxlByRows%2C%20searchdirection%3A%3DxlPrevious%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse).Row%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E%3CBR%20%2F%3EWorksheets(%22ALL%20DATA%22).Cells(PLastRow%20%2B%202%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ENext%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENext%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20I%20am%20trying%20to%20copy%20data%20from%20different%20worksheets%20to%20one%20worksheet%20(%22ALL%20DATA%22)%2C%20in%20the%20same%20workbook.%20All%20worksheets%20have%20different%20data%20range.%26nbsp%3B%3C%2FP%3E%3CP%3E-%20I%20defined%20two%20variables%20to%20hold%20last%20column%20and%20last%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E-%20I%20get%20an%20error%20at%20the%20line%20where%20I%20select%20the%20range%20of%20data%20to%20be%20copied%20-%22Worksheets(i).Rows(tablerange).Select%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
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.
Highlighted

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