VBA code to select all the data on a worksheet

[Shweta]

Hi All,

Please help me out with a VBA code to select all the data on a worksheet.
I am using the following code:

Sheets(1).Select
Range("A1").Select
On Error Resume Next
mylastrow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
mylastcol = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcol).Address
myrange = "A2:" & mylastcell
Range(myrange).Select
Selection.Copy

I am not getting what is the problem with this code. It works sometimes and sometimes not.

Please help me out on this.

Thanks!

Regards,
Shweta

 

[prajul89]

Sheets(1).Select
Range("A1").Select
On Error Resume Next
Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
mylastcelladd = Cells(mylastcell.Row, mylastcell.Column).Address
myrange = "A2:" & mylastcelladd
Range(myrange).Select
Selection.Copy

use this instead.
Your codes do work for me though.

 

[Shweta]

Thanks Prajul for your quick response!

your code is doing the same as my code is doing. It is selecting only range("A1")

 

[prajul89]

Then the only possibility is that you entire sheet is either completely blank or only first row has some values.

 

[Shweta]

I have data in range("A1:C5")

 

[prajul89]

I will say just remove that on error statement and see what happens.

 

[Michael M]

Why not....

Sub alldat()
ActiveSheet.UsedRange.Copy
End Sub

 

[Shweta]

Thanks Michael!
Your code is working fine.

would like to tell you that my code and prajul's code is working good on another file.

Regards,
Shweta

 

[jackfox68]

what about using this

Cells.Select

That seems to work for me. You would just need to specify which sheet.

 

[MilesS]

Thanks Michael, works perfectly.
Prajul, no also only selected one cell with my data.
jacfox68, no that selects the whole sheet and only works if you are pasting the whole sheet into A1 on another sheet.

I have multiple files (each only one sheet and about 700 lines), I want to copy all [active] cells to one new workbook. so I used a dir loop to open each file sequentially, Michael's used range, ActiveCell.SpecialCells(xlLastCell).Offset(2, 0).End(xlToLeft).Select to paste into my combined file
(and a few other lines of coding before someone says that didn't work for them... )