Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command to make a forumula fron not running | Excel Discussion (Misc queries) | |||
Command Button Problem | Excel Discussion (Misc queries) | |||
Problem with command button name | Excel Discussion (Misc queries) | |||
Checking to see that a CHART exists prior to running a simple command | Excel Discussion (Misc queries) | |||
strange command button problem | Excel Discussion (Misc queries) |