Excel copy selected data

I am trying to copy data from Sheet1 to Sheet2. Can anyone help me to do that.

I have tried these.
Worksheets("Sheet2").Range("A1:S1000").Value = Worksheets("Sheet1").Range("A1:S1000").Value

This copies A1 to S1000 data. I want to set these value dynamically to copy only the cells having data. My data starts from A1.


With this, I am able to copy the header. How can I expand this to copy all rows also.

With Worksheets("Sheet1").Range("A1")
  .Range(.Cells(1), .End(xlToRight)).Copy Destination:= Worksheets("Sheet2").Range("A1")
End With

Thanks in advace.
Sharath
LVL 41
SharathData EngineerAsked:
Who is Participating?
 
MichaelConnect With a Mentor Business AnalystCommented:
Hi Sharath,

you can use
With Worksheets("Sheet1")
  .Range("A1").Currentregion.Copy Destination:= Worksheets("Sheet2").Range("A1")
End With

Open in new window

However, this assumes there are no empty rows or empty columns in your dataset

You can also use:
to determine the last used row in a sheet:
.Cells(.Rows.Count, "A").End(xlUp).Row

Open in new window

and determine the last column:
.Cells(1, .Columns.Count).End(xlToLeft).Column

Open in new window


So, the determine the dataset try this:
With Worksheets("Sheet1")
  .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column)).Copy Destination:= Worksheets("Sheet2").Range("A1")
End With

Open in new window

0
 
SharathData EngineerAuthor Commented:
Thanks JazzyJoop. I tried your first suggestion and it is working. Will check the 2nd one also and close the question. Thanks again.
0
 
SharathData EngineerAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.