May 22 2020 02:00 AM
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?
May 24 2020 11:37 PM
May 28 2020 03:11 AM
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