Trouble shoot vba code fine last cell

[jpsanicky]

Greetings all;
I have a find last column to begin pasting data in Col A:X (returned row)

The code is used earlier in the same procedure with no issue
When I use the code a second time it errors out at Cell.Select

Data is already in All Orgs Prod.xlsx A2:A;500

Here is the code for the first use;
Call P_Org_Update 'opens prod and non prod
Workbooks("P_Org.xlsx").Activate

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:W" & LastRow).Select
Selection.Copy

Workbooks("All Orgs Prod Update.xlsx").Activate

' Find last row


Dim ws As Worksheet
Set ws = ActiveSheet


For Each cell In ws.Columns(1).Cells
If Len(cell) = 0 Then cell.Select: Exit For
Next cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Workbook All Orgs Prod.xlsx is properly populated


Second use of code;

Workbooks("P NON Prod Update.xlsx").Activate

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:W" & LastRow).Select
Selection.Copy

Workbooks("All Orgs Prod Update.xlsx).Activate

' Find last row


' Dim ws As Worksheet remarked out since dim already in routine
' Set ws = ActiveSheet remarked out since dim already in routine


For Each cell In ws.Columns(1).Cells
If Len(cell) = 0 Then cell.Select: Exit For This where the routine stops??
Next cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

What am i doing wrong??

Thanks JPS

 

[MARK858]

Uncomment the line beginning with Set.

 

[jpsanicky]

Mark858
Thanks, staring right at it
All is good
JPS

 

[MARK858]

Happy it helped (but not the way I would find the next blank cell/ Last Row in a column though)

 

[jpsanicky]

Hi Mark858, Thanks again. If you have a more efficient way I'm eager to learn and maybe update my project.

 

[MARK858]

Try replacing all of...

Workbooks("P NON Prod Update.xlsx").Activate

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:W" & LastRow).Select
Selection.Copy

Workbooks("All Orgs Prod Update.xlsx).Activate

' Find last row


' Dim ws As Worksheet remarked out since dim already in routine
' Set ws = ActiveSheet remarked out since dim already in routine


For Each cell In ws.Columns(1).Cells
If Len(cell) = 0 Then cell.Select: Exit For This where the routine stops??
Next cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

with the (untested) code below

    With Workbooks("P NON Prod Update.xlsx").Sheets("Sheet1")
        .Range("A2:W" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
        Workbooks("All Orgs Prod Update.xlsx").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
    End With
    Application.CutCopyMode = False

Test on copies of your workbooks