Range Selection as part of wider VBA

Jimmers

New Member
Joined
Jul 8, 2019
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a Macro which pings an e-mail for a team confirming that a tracker has been updated, while at the same time updating said tracker.

The tracker update is a standard copy and paste, with a range selections as follows;

VBA Code:
Range("A2:W2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

This works fine if there is more than one line on the form to copy and paste. However when there is just one line it copies the entire sheet, which causes havoc with the code when I then try and paste into the tracker.

The full code is as follows;

VBA Code:
Range("A2:W2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
  Workbooks("Milestones Change Request Tracker.xlsx").Activate
 Sheets("Tracker").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues

Any ideas how I can stop this?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Perhaps this suffices?
VBA Code:
Range("A2:W2").CurrentRegion.Copy
With Workbooks("Milestones Change Request Tracker.xlsx").WorkSheets("Tracker")
    .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
 
Upvote 0
Hi Jan,

The Debugger popped up using this at
VBA Code:
Range("A2:W2").CurrentRegion.Copy
 
Upvote 0
Yes not all cells in that range, but most, there won't necessarily always be something in every cell in the range. It may be 3/4s full.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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