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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Does this work ?

VBA Code:
Option Explicit

Sub cpypste()
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("Sheet1")
Set wsDest = Worksheets("Sheet2")

'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, 1).End(xlUp).Offset(1).Row

'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

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

'wsDest.Range ("A" & lDestLastRow)
wsDest.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'Optional - Select the destination sheet
wsDest.Activate

End Sub
 
Upvote 0
code seems to do all the wrong things
PLS Help
 
Upvote 0
Thank you for reply
It seems that it copies all lines from 6 to 163 even if blank and not only lines with values
Next time I add to list it pastes below the blank lines and starts pasting at line 164
Otherwise the code you provided seems to work
Thank you
 
Last edited:
Upvote 0
to clarify the source I am Copying from is from an advanced filter
Every time I change a dropdownlist it clears and does the advanced filter again. between those actions I clear the filter area and re-run advanced filter and copy to target before filter cleared.
 
Upvote 0
Once Copied I see that lines 6 to 163 is highlighted on destination
 
Upvote 0
You have first FILTERED the sheet to be copied and it is only the filtered rows that you want copied. Is that correct ?

If you copy from the filtered sheet more than once, you want the new information pasted over the old data. Is that correct ?
 
Upvote 0
Changed my Advanced filter from Unique:=False to Unique:=True
This resolved copying all the blank lines.
It now only leaves one blank line between every paste I make
My code looks like this:

VBA Code:
Sub cpypste()
Call TurnStuffOff
    Call Clear_PO_Filter
    Call Adv_Filter_PO
    
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")

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

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

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

'wsDest.Range ("B" & lDestLastRow)
wsDest.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'Optional - Select the destination sheet
wsDest.Activate
Call TurnStuffOn
End Sub

Somewhere in this code it is creating one blank row and then pastes the next data I selected. Can't seem to find it.
PLS assist
Thank you
 
Upvote 0
'wsDest.Range ("B" & lDestLastRow)
wsDest.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Remove the Offset(1, 0)
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,697
Members
448,293
Latest member
jin kazuya

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