Excel VBA - Alternative to Copy/Paste with xlToRight xlDown

NigelG5

New Member
Joined
Jan 30, 2017
Messages
4
I am trying to simplify the VBA below as there's too many selects and with the array of data being copied from one sheet to the other as the sheets get larger in size, I want to reduce the overhead and increase the speed it executes. It currently works, but isn't the cleanest.

The "TODAYS_DATA" tab contains the output of an Oracle SQL extraction that has been imported into Excel. What I want to do, is take that tabs content (from A2 across to the end and then down to capture the whole sheet), then paste it at the bottom of the tens of thousands of rows that exist in the "HISTORIC_DATA" tab already. I can then save the file and repeat this each day.

As I say, whilst this works, I am aware that there's no need to select cells and copy/cut/paste in this way is best avoided.

Code:
Sheets("TODAYS_DATA").Select
    Range("A2").Select   ' Using A2 as no need to copy of the heading row
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    'Paste that copied data from the last working day and paste it into the Historic_Data tab
    Sheets("HISTORIC_DATA").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    Range("A1").Select
I have seen this as an alternative, but cannot get it to work no matter how I try and shoehorn it into what I already have.


Code:
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Value

Any pointers would be good as trying to tidy up my code as I go along.

Thanks all :)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
this would copy your data over.

Code:
Sub t()
Sheets("TODAYS_DATA").UsedRange.Offset(1).Copy Sheets("HISTORIC_DATA").Cells(Rows.Count, 1).End(xlUp)(2)
End Sub
 
Upvote 0
this would copy your data over.

Code:
Sub t()
Sheets("TODAYS_DATA").UsedRange.Offset(1).Copy Sheets("HISTORIC_DATA").Cells(Rows.Count, 1).End(xlUp)(2)
End Sub
Thank you for that, it's certainly more streamlined than my original attempt and understand what that's all doing :)
 
Upvote 0
Code:
ActiveSheet.Range(ActiveCell, Range(ActiveCell).End(xlDown).End(xlToRight)).Select
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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