copy and paste problem in vb

[Blubber]

I have a sheet called DrListWorkCopy which data i would like to duplicate in
DrListWorkCopy1. DrLIstWorkCopy1 exists as an empty sheet with headers on row
1 identical to DrLIstWorkCopy. The Macro works well when there is data in
DrListWorkCopy. However, in the occasion when DrListWorkCopy happen to have
no data in it, the macro coppies the headers and paste it onto
DrListWorkCopy1.

How do I prevent that? I think it has got to do with how I coded the last
empty row in DrListWorkCopy1.

The codes are as follows:

Dim SourceWks As Worksheet
Dim TargetWks As Worksheet

Set SourceWks = Sheets("DrListWorkCopy")
Set TargetWks = Sheets("DrListWorkCopy1")

SourceWks.Range("A2:AS" & SourceWks.Range("AS65536").End(xlUp).Row).Copy
TargetWks.Select
Range("A2").Select
TargetWks.Paste

Would appreciate some help.

 

[Kevin B]

Try the following, the lines that end with ". _" (dot, space, underscore) are
valid VBA line breaks to compensate for any errant word wrapping:

Dim wsSource As Worksheet
Dim wsTarget As Worksheet

Set wsSource = ThisWorkbook.Worksheets("DrListWorkCopy")
Set wsTarget = ThisWorkbook.Worksheets("DrListWorkCopy1")
wsSource.Range(.Range("A2:AS" & SourceWks. _
Range("AS65536").End(xlUp).Row).Copy). _
Copy Destination:=wsTarget.Range("A2")

Set wsSource = Nothing
Set wsTarget = Nothing

 

[Don Guillett]

Sub copyem()
Dim SourceWks As Worksheet
Dim TargetWks As Worksheet
Set SourceWks = Sheets("sheet2")
Set TargetWks = Sheets("sheet3")
With SourceWks
lastrow = Application.Max(2, .Cells(Rows.Count, "a").End(xlUp).Row)
..Range("A2:AS" & lastrow).Copy TargetWks.Range("A2")
End With
End Sub

 

[Blubber]

Thanks Kevin,

I copy and paste the codes and ran out of room in the posting box. Thats not
a problem.

Problem is logic. It could be the "xlup" . When the SourceWks is empty, it
copies the headers because that happens to be the last row with data.

I want to find a way to prevent that.

 

[Blubber]

That worked. Thanks Don

Sub copyem()
Dim SourceWks As Worksheet
Dim TargetWks As Worksheet
Set SourceWks = Sheets("sheet2")
Set TargetWks = Sheets("sheet3")
With SourceWks
lastrow = Application.Max(2, .Cells(Rows.Count, "a").End(xlUp).Row)
..Range("A2:AS" & lastrow).Copy TargetWks.Range("A2")
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help.