• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Paste Range

mistryman

New Member
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.
 
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:
Code:
lastRow = Cells(Rows.Count,"A").End(xlUp).Row
 
[/CODE]Thanks Luke.

I gave it a shot but I am getting a run time error
Code:
Sub copypaste()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:&lastrow").Copy
 
End Sub
 
VB variables need to be outside the quotation marks.
Code:
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.
 
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.
 
Hey Luke

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

Code:
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
 
Back
Top