Copy variable range from one worksheet and paste values into second worksheet at the next empty row below.

BPW

New Member
Joined
Mar 7, 2013
Messages
27
Office Version
  1. 365
Platform
  1. Windows
I was wondering if anyone would be able to help me get un-stuck with a wrinkle in my macro. What I'm trying to do is copy a variable range on WS1 and paste values to another sheet at the next empty row below. The idea is to enter data in WS1 and click a button that will copy that data and paste (values) into WS2, appending to any previously pasted data.

My VBA is below. Is there any advice on where I can modify the existing code to add that appending ability? Or is this code poor approach and I should go about it another way?

Please let me know if additional context or information is needed.

Thanks in advance!

Barry


VBA Code:
Sub CSV_Creation()


Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("CSV")
Set ws2 = Worksheets("CSV EXPORT")
Dim Ary As Variant
Dim LastRow As Long

   
'First SKU to CSV Export tab from CSV

LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
Ary = Application.Unique(ws1.Range("A1:C" & LastRow).Value)
ws2.Range("A1:C" & LastRow).Resize(UBound(Ary)).Value = Ary


End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
 
  • Like
Reactions: BPW
Solution

BPW

New Member
Joined
Mar 7, 2013
Messages
27
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
This works perfectly! Thanks Fluff for getting me un-stuck!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
  • Like
Reactions: BPW

Forum statistics

Threads
1,140,928
Messages
5,703,220
Members
421,283
Latest member
MacroBegin

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
Top