Problem with Copy to another sheet in VBA

oblix

Board Regular
Joined
Mar 29, 2017
Messages
183
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2011
  7. 2010
  8. 2007
  9. 2003 or older
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi there
The code I am Trying to get to copy from one sheet to another is:

Sub Copy_To_Received()

Call Clear_PO_Filter
Call Adv_Filter_PO

'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Worksheets("PO")
Set wsDest = Worksheets("Received")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "AS").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

'3. Copy & Paste Data
wsCopy.Range("AS6:AZ" & lCopyLastRow).Copy
wsDest.Range ("B" & lDestLastRow)

'Optional - Select the destination sheet
wsDest.Activate

End Sub

The problem is that it flags me with an error:
wsDest.Range ("B" & lDestLastRow) ".Range seems to be issue

Can some one please assit?
Thank you
 
Don't want the new information to paste over old data, want to paste in the next blank row. Currently it leaves one blank and then copies on following row
 

Attachments

  • screenshot.png
    screenshot.png
    70.8 KB · Views: 4
Last edited:
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Thank you for sticking with me Logit
I tried that but then It overides my headers on the first round of copying
testing the advanced filter I found that if I use SHFT+CTRL+* on the advance filter target range it shows a nother last blank row....
Can that be the problem?
Here is the ADV filter code:

VBA Code:
Sub Adv_Filter_PO()

    Sheets("PO").Select
    Range("AA5:AH166").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Range("AJ5:AQ7"), CopyToRange:=Range("AS5:AZ5"), Unique:=True
End Sub

Once again thank you for patience
 
Upvote 0
It turns out there was something wrong with the criterea in my advanced filter
I added this formula: =LEN(AF6)>0 and it solved my problem.

Thanks for Logit for tryingto help
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top