Problem with VB running from a command button
 

[win 2k, office 2k]

I have some VB which takes a filtered list (sheet 1), pastes the values to a
new sheet (sheet 2), then sorts it (another sheet (sheet 3) has a chart
which is based on sheet 2 values). the process is started by clicking a
command button on sheet 1.

everything works until the sorting stage, but it constantly wants to refer
to the first sheet (which contains the command button). how can i make the
sorting stage refer to the correct sheet?

(code is below...)

Many thanks,

tim


Code

Private Sub CommandButton4_Click()

Dim Xfer, Data

'sheet 2
Set Xfer = Worksheets("Transfer")

'sheet 1
Set Data = Worksheets("Data")

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.CurrentRegion.Select
Selection.ClearContents

'sheet 1
Data.Select
Data.Range("A2").Select
Selection.CurrentRegion.Select
Selection.Copy
Range("A1").Select

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Sheets("Transfer").Select
Xfer.Range("B2").Select
'this is where the problem is
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

Problem with VB running from a command button
 

Hi,

probably you may have to seprate sheet and range
selection like this:

Sheets("Transfer").Select
Xfer.select
Range("B2").Select
'this is where the problem is
Selection.Sort Key1:=Range("B2"),
Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom



Abdul Salam
-----Original Message-----
[win 2k, office 2k]

I have some VB which takes a filtered list (sheet 1),
pastes the values to a
new sheet (sheet 2), then sorts it (another sheet (sheet
3) has a chart
which is based on sheet 2 values). the process is
started by clicking a
command button on sheet 1.

everything works until the sorting stage, but it
constantly wants to refer
to the first sheet (which contains the command button).
how can i make the
sorting stage refer to the correct sheet?

(code is below...)

Many thanks,

tim


Code

Private Sub CommandButton4_Click()

Dim Xfer, Data

'sheet 2
Set Xfer = Worksheets("Transfer")

'sheet 1
Set Data = Worksheets("Data")

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.CurrentRegion.Select
Selection.ClearContents

'sheet 1
Data.Select
Data.Range("A2").Select
Selection.CurrentRegion.Select
Selection.Copy
Range("A1").Select

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Sheets("Transfer").Select
Xfer.Range("B2").Select
'this is where the problem is
Selection.Sort Key1:=Range("B2"),
Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

End Sub


.

Problem with VB running from a command button
 

Unqualified ranges in a sheet module refer to the sheet containing the code:

Private Sub CommandButton4_Click()

Dim Xfer, Data

'sheet 2
Set Xfer = Worksheets("Transfer")

'sheet 1
Set Data = Worksheets("Data")

'sheet 2
Xfer.Range("A1").CurrentRegion.ClearContents

'sheet 1
Data.Range("A2").CurrentRegion..Copy


'sheet 2
With Xfer.Range("A1")
.PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, SkipBlanks:= False, _
Transpose:=False
.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= False, _
Transpose:=False
End With

Xfer.Range("B2").Sort Key1:=Xfer.Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub


--
Regards,
Tom Ogilvy



Tim Marsh wrote in message
...
[win 2k, office 2k]

I have some VB which takes a filtered list (sheet 1), pastes the values to
a
new sheet (sheet 2), then sorts it (another sheet (sheet 3) has a chart
which is based on sheet 2 values). the process is started by clicking a
command button on sheet 1.

everything works until the sorting stage, but it constantly wants to refer
to the first sheet (which contains the command button). how can i make
the
sorting stage refer to the correct sheet?

(code is below...)

Many thanks,

tim


Code

Private Sub CommandButton4_Click()

Dim Xfer, Data

'sheet 2
Set Xfer = Worksheets("Transfer")

'sheet 1
Set Data = Worksheets("Data")

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.CurrentRegion.Select
Selection.ClearContents

'sheet 1
Data.Select
Data.Range("A2").Select
Selection.CurrentRegion.Select
Selection.Copy
Range("A1").Select

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False

Sheets("Transfer").Select
Xfer.Range("B2").Select
'this is where the problem is
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

Problem with VB running from a command button
 

You do things in your code which are not necessary. You
select a worksheet then refer to the sheet using a variable
set to that worksheet. This makes your code is a bit
muddled and messy and is not doing things the "easy" way.

Private Sub CommandButton4_Click()
Dim Xfer, Data As Worksheet
Dim DestRange As Range

Set Data = Worksheets("Data") ' sheet 1
Set Xfer = Worksheets("Transfer") ' sheet 2
Set DestRange = Xfer.Range("E5").CurrentRegion ' detination range

DestRange.ClearContents

Data.Range("A2").CurrentRegion.Copy DestRange
DestRange = DestRange.Value

DestRange.CurrentRegion.Sort key1:=DestRange.Range("A1")

End Sub

This is a "nicer" way to do it.

Chrissy

Tim Marsh wrote
[win 2k, office 2k]

I have some VB which takes a filtered list (sheet 1), pastes the values to a
new sheet (sheet 2), then sorts it (another sheet (sheet 3) has a chart
which is based on sheet 2 values). the process is started by clicking a
command button on sheet 1.

everything works until the sorting stage, but it constantly wants to refer
to the first sheet (which contains the command button). how can i make the
sorting stage refer to the correct sheet?

(code is below...)

Many thanks,

tim


Code

Private Sub CommandButton4_Click()

Dim Xfer, Data

'sheet 2
Set Xfer = Worksheets("Transfer")

'sheet 1
Set Data = Worksheets("Data")

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.CurrentRegion.Select
Selection.ClearContents

'sheet 1
Data.Select
Data.Range("A2").Select
Selection.CurrentRegion.Select
Selection.Copy
Range("A1").Select

'sheet 2
Xfer.Select
Xfer.Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Sheets("Transfer").Select
Xfer.Range("B2").Select
'this is where the problem is
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub