Copy Paste Range

[mistryman]

Hi Everyone

I was looking for some help on creating a macro with the following functionality. I have two sheets AllData and SelectData.

AllData has numerous columns that are unnecessary and I would like to copy and paste the necessary data into Select. The issue I am having is that because this is a daily task that I must complete the range is not always the same. essentially I would like to Select and Copy columns A,B,D in AllData until the first empty cell and paste into A,B,C in Select Data.


Any help would be greatly appreciated.

 

[Luke M]

I'd suggest first taking a look at using AdvancedFilter. Let's you specify which columns you want to copy, and you can define where the range is. Last Used row can be found via:

lastRow = Cells(Rows.Count,"A").End(xlUp).Row

 

[mistryman]

[/CODE]Thanks Luke.

I gave it a shot but I am getting a run time error

Sub copypaste()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:&lastrow").Copy
 
End Sub

 

[Luke M]

VB variables need to be outside the quotation marks.

Sub copypaste()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:" & lastrow).Copy

End Sub

PS. If your last row might be greater than 65536, you will want to define lastRow as a Long rather than Integer, as Integer only goes from -65536 to +65536.

 

[mistryman]

Hey Luke

It is still not working should I tried taking out the semi colon with no avail.

When I take out the semi colon it doesn't error out but what it does not give the intended result.

 

[mistryman]

Hey Luke

I figured it out the code is below. Thanks for all your help

Sub copypaste()
Dim lastrow As Long
lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Worksheets("AllData").Range("A2:A" & lastrow).Copy
  Worksheets("SelectData").Range("A2:A" & lastrow).PasteSpecial
 
End Sub