Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Paste problem | Excel Discussion (Misc queries) | |||
Copy and paste problem | Excel Discussion (Misc queries) | |||
Copy and Paste Problem | Excel Discussion (Misc queries) | |||
copy paste problem? | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) |