VBA not working - Copy Sheet data from 1 sheet to other sheet in same workbook

[airoboml]

Hi Team ,

Basically i need to copy the rows and cols which are not fixed, so it can increase daily from one sheet to another sheet in the same workbook.

I am getting error in the end as "Run-time error '5' Invalid procedure call or argument, can you please help.


Below is the vba code:

Sub consolidate()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim wsAll As Worksheet
Set wsAll = wb.Worksheets("All")
Dim ws1 As Worksheet
Set ws1 = wb.Worksheets("S1")
Dim lastRowAll As Long
Dim lastRow1 As Long
Dim lastCol1 As Long


lastRowAll = wsAll.Cells(wsAll.Rows.Count, "A").End(xlUp).Row + 1
lastRow1 = ws1.Range("A1").End(xlDown).Row
lastCol1 = ws1.Range("A1").End(xlToRight).Column
ws1.Activate

Cells(lastRow1, lastCol1).Copy

wsAll.Activate
ActiveSheet.Cells("A" & lastRowAll).Select
Application.CutCopyMode = False


MsgBox ("Job Complete!!")
End Sub


Thanks,
SK

 

[Skyybot]

?

 

[Skyybot]

?

Disregard please.

 

[Skyybot]

Is there a copy of your Workbook that you can share?

 

[airoboml]

Is there a copy of your Workbook that you can share?

Is there a copy of your Workbook that you can share?

Attachment provided in the link

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

 

[airoboml]

Hi Team ,

Basically i need to copy the rows and cols which are not fixed, so it can increase daily from one sheet to another sheet in the same workbook.

I am getting error in the end as "Run-time error '5' Invalid procedure call or argument, can you please help.


Below is the vba code:

Sub consolidate()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim wsAll As Worksheet
Set wsAll = wb.Worksheets("All")
Dim ws1 As Worksheet
Set ws1 = wb.Worksheets("S1")
Dim lastRowAll As Long
Dim lastRow1 As Long
Dim lastCol1 As Long


lastRowAll = wsAll.Cells(wsAll.Rows.Count, "A").End(xlUp).Row + 1
lastRow1 = ws1.Range("A1").End(xlDown).Row
lastCol1 = ws1.Range("A1").End(xlToRight).Column
ws1.Activate

Cells(lastRow1, lastCol1).Copy

wsAll.Activate
ActiveSheet.Cells("A" & lastRowAll).Select
Application.CutCopyMode = False


MsgBox ("Job Complete!!")
End Sub


Thanks,
SK

Excel attached in the link, unable to find option to upload excel

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

 

[airoboml]

Is there a copy of your Workbook that you can share?

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

 

[Micron]

Please post code between code tags (vba button on posting toolbar) to maintain indentation and readability.
Try ActiveSheet.Range("A" & lastRowAll).Select
It is usually not a good idea to activate and select sheets or ranges because they can change as the code progresses or you make mistakes in thinking which is the active sheet. Also it is seldom necessary and tends to slow code down over large ranges or loops. Especially true if you've set the sheets or ranges as objects (which you have) - you might as well refer to the object variables. F'rinstance, this should be enough:
ws1.Cells(lastRow1, lastCol1).Copy

EDIT - I will have to review all of this later. I had my post holding in limbo for about 3 hours because it would not commit when I clicked on the submit button.

 

[MARK858]

This line

ActiveSheet.Cells("A" & lastRowAll).Select

should be Range not Cells

ActiveSheet.Range("A" & lastRowAll).Select

You also aren't pasting anything in the code (you're only selecting the cell)

You do also realise that the line below is only copying s single cell?

Cells(lastRow1, lastCol1).Copy

You probably mean something more like

Sub consolidate()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim wsAll As Worksheet
    Set wsAll = wb.Worksheets("All")
    Dim ws1 As Worksheet
    Set ws1 = wb.Worksheets("S1")
    Dim lastRowAll As Long
    Dim lastRow1 As Long
    Dim lastCol1 As Long


    lastRowAll = wsAll.Cells(wsAll.Rows.Count, "A").End(xlUp).Row + 1
    lastRow1 = ws1.Range("A1").End(xlDown).Row
    
    lastCol1 = ws1.Range("A1").End(xlToRight).Column


    Range(ws1.Cells(1, "A"), ws1.Cells(lastRow1, lastCol1)).Copy wsAll.Range("A" & lastRowAll)
   

    MsgBox ("Job Complete!!")
End Sub