Problem with VB running from a command button

  • Thread starter Thread starter Tim Marsh
  • Start date Start date
T

Tim Marsh

[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
 
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,
Click to expand...
Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues,
Click to expand...
Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Sheets("Transfer").Select
Xfer.Range("B2").Select
'this is where the problem is
Selection.Sort Key1:=Range("B2"),
Click to expand...
Order1:=xlAscending, Header:=xlGuess,
 
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
 
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
 
Back
Top