Range problems
 

i am gettin an error evertime the run gets to the range("name").selec
can any one help me identify the problem







Private Sub cmdVan1_Click()
Sheets("Van 1 ").Select
Range("B2:B8").Select
Selection.Copy
Sheets("Work Order").Select
Range("B2:B8").Select
ActiveSheet.Paste
Sheets("Van 1 ").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("B12").Select
ActiveSheet.Paste
Sheets("Van 1 ").Select
Range("E10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("C12").Select
ActiveSheet.Paste
Sheets("Service schedule").Select
Range("B2:C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("B11:C11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
Range("B2:B8").Selec

--
Message posted from http://www.ExcelForum.com

Range problems
 

Your code worked fine for me. Are you sure the sheet name "Van 1 " has an
extra space at the end?

Regards,
Wes

Range problems
 

fred 616

It sounds like your botton has focus

Try changing the button properties

TakeFocusOnClick = false


Also you donot need to select sheets and ranges to copy and paste t
them. Selecting makes macro's run slower


try this modifaction to your code

Private Sub cmdVan1_Click()
Dim wsVan As Worksheet
Dim wsWO As Worksheet

Set wsVan = Sheets("Van 1 ")
Set wsWO = Sheets("Work Order")

wsVan.Range("B2:B8").Copy Destination:=wsWO.Range("b2:b8")

wsVan.Range("c10").Copy Destination:=wsWO.Range("b12")

wsVan.Range("e10").Copy Destination:=wsWO.Range("c12")

wsVan.Range("B2:c2").Copy Destination:=wsWO.Range("b11:c11"

--
Message posted from http://www.ExcelForum.com

Range problems
 

If your code is in a general module, then the unqualified ranges refer to the
active sheet.

But if your code is behind a worksheet (like when you run it from clicking on a
commandbutton from the control toolbox toolbar placed on the worksheet), then
the unqualified ranges refer to the sheet that owns the code.

You could modify your code this way:

Option Explicit
Private Sub cmdVan1_Click()

With Sheets("Van 1 ")
.Select
.Range("B2:B8").Select
Selection.Copy
End With
With Sheets("Work Order")
.Select
.Range("B2:B8").Select
ActiveSheet.Paste
End With
With Sheets("Van 1 ")
.Select
.Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
End With
With Sheets("Work Order")
.Select
.Range("B12").Select
ActiveSheet.Paste
End With
With Sheets("Van 1 ")
.Select
.Range("E10").Select
Application.CutCopyMode = False
Selection.Copy
End With
With Sheets("Work Order")
.Select
.Range("C12").Select
ActiveSheet.Paste
End With
With Sheets("Service schedule")
.Select
.Range("B2:C2").Select
Application.CutCopyMode = False
Selection.Copy
End With
With Sheets("Work Order")
.Select
.Range("B11:C11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
.Range("B2:B8").Select
End With
End Sub

But even easier (to understand and write):

Option Explicit
Private Sub cmdVan1_Click()

Sheets("Van 1 ").Range("B2:B8").Copy _
Destination:=Sheets("Work Order").Range("B2")

Sheets("Van 1 ").Range("C10").Copy _
Destination:=Sheets("Work Order").Range("B12")

Sheets("Van 1 ").Range("E10").Copy _
Destination:=Sheets("Work Order").Range("C12")

Sheets("Service schedule").Range("B2:C2").Copy _
Destination:=Sheets("Work Order").Range("B11")

Sheets("service schedule").PrintOut Copies:=2, Collate:=True

End Sub

Notice that I changed the "To" range to just the topleftcell. I'd let excel
figure out how big a range to paste (based on the copied range).



"fred 616 <" wrote:

i am gettin an error evertime the run gets to the range("name").select
can any one help me identify the problem

Private Sub cmdVan1_Click()
Sheets("Van 1 ").Select
Range("B2:B8").Select
Selection.Copy
Sheets("Work Order").Select
Range("B2:B8").Select
ActiveSheet.Paste
Sheets("Van 1 ").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("B12").Select
ActiveSheet.Paste
Sheets("Van 1 ").Select
Range("E10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("C12").Select
ActiveSheet.Paste
Sheets("Service schedule").Select
Range("B2:C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("B11:C11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
Range("B2:B8").Select

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Range problems
 

Since your code is the event code for a commandbutton on a worksheet, the
code is located in the sheet module

When you do Sheets("Work Order").Select
then the sheet Work Order becomes the active sheet and the sheet with the
button (sheet Van 1) is not longer active. You then do
Range("B2:B8").Select. Your intent it to select this on sheets Work Order,
but since it is unqualified with the sheet name and located in the module
for Van 1, it actually refers to B2:B8 on Sheet Van 1. Since Van 1 is not
the active sheet, the range can not be selected and your code fails.

You can move all the code to a general module and call it from your Click
event or you can fully qualify your references ( and skip the selecting as
well since it isn't necessary).

Private Sub cmdVan1_Click()
Sheets("Van 1 ").Range("B2:B8").Copy _
Destination:=Sheets("Work Order").Range("B2:B8")
Sheets("Van 1 ").Range("C10").Copy _
Destination:=Sheets("Work Order").Range("B12")
Sheets("Van 1 ").Range("E10").Copy _
Destination:=Sheets("Work Order").Range("C12")
Sheets("Service schedule").Range("B2:C2").Copy _
Destination:=Sheets("Work Order").Range("B11:C11")
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
End Sub

This assumes you are printing out Sheets("Van 1") [ which is consistent
with your code]

if not change your printout to

Sheets("Work Order").Printout Copies:=2, Collate:=True

as an example.

--
Regards,
Tom Ogilvy


"fred 616 " wrote in message
...
i am gettin an error evertime the run gets to the range("name").select
can any one help me identify the problem







Private Sub cmdVan1_Click()
Sheets("Van 1 ").Select
Range("B2:B8").Select
Selection.Copy
Sheets("Work Order").Select
Range("B2:B8").Select
ActiveSheet.Paste
Sheets("Van 1 ").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("B12").Select
ActiveSheet.Paste
Sheets("Van 1 ").Select
Range("E10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("C12").Select
ActiveSheet.Paste
Sheets("Service schedule").Select
Range("B2:C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work Order").Select
Range("B11:C11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
Range("B2:B8").Select


---
Message posted from http://www.ExcelForum.com/

Range problems
 

thanks for the help i have the copy working now but now i need it t
print the work order sheet and then clear out the copied cell

--
Message posted from http://www.ExcelForum.com

Range problems
 

Fred

Not knowing how you have coded you macro I am pasting this code base
on my earlier suggestion modification


wsWO.PrintOut Copies:=1, Collate:=True

wsWO.Range("b2:b8", "b12", "c12", "b11:c11").ClearContent

--
Message posted from http://www.ExcelForum.com

Range problems
 

Private Sub cmdVan1_Click()
Sheets("Van 1 ").Range("B2:B8").Copy _
Destination:=Sheets("Work Order").Range("B2:B8")
Sheets("Van 1 ").Range("C10").Copy _
Destination:=Sheets("Work Order").Range("B12")
Sheets("Van 1 ").Range("E10").Copy _
Destination:=Sheets("Work Order").Range("C12")
Sheets("Service schedule").Range("B2:C2").Copy _
Destination:=Sheets("Work Order").Range("B11:C11")
Sheets("Work Order").PrintOut Copies:=2, Collate:=True
Worksheets("Work Order").Range("B2:B8,B12,C12").Clearcontents
End Sub

--
Regards,
Tom Ogilvy


"fred 616 " wrote in message
...
thanks for the help i have the copy working now but now i need it to
print the work order sheet and then clear out the copied cells


---
Message posted from http://www.ExcelForum.com/