copy and paste problem in vb
 

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.

copy and paste problem in vb
 

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

--
Kevin Backmann


"Blubber" wrote:

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.

copy and paste problem in vb
 

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

"Blubber" wrote in message
...
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.

copy and paste problem in vb
 

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.



"Kevin B" wrote:

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

--
Kevin Backmann


"Blubber" wrote:

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.

copy and paste problem in vb
 

That worked. Thanks Don

"Don Guillett" wrote:

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

"Blubber" wrote in message
...
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.

copy and paste problem in vb
 

Glad to help.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Blubber" wrote in message
...
That worked. Thanks Don

"Don Guillett" wrote:

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

"Blubber" wrote in message
...
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.