Data Validation List Macro

[mil_tech]

Hay all,
I am working in Excel 2010, I created 2 Macros that copy colored cells from one worksheet to another, and they work fine. I also created a Data Valadiation drop down list and I am calling the Macro from with in the Data Validation list. The script runs but is cycles over and over until it eventually fails. can someone please let me know if I am donig something wrong here, thanks.
Data Validation list Code:
[VBA]Select Case Range("C4")
Case "DC OFFICE"
DC
Case "LA OFFICE"
Case "NY OFFICE"
Case "MU OFFICE"
MU
End Select
End Sub[/VBA]

MACRO CODE
[VBA]Sub DC()
'
' DC Macro
'
'
Sheets("Sheet3").Select
Range("C2:H8").Copy
Sheets("Sheet1").Select
Range("C6:H12").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C6").Select

End Sub[/VBA]

[SamT]

Welcome to the forum, Mil Tech.

I cleaned up your macro code, but I did not change it's flow. I suspect that the problem is with the ActiveSheet.Paste line. Sheet.Select does not activate a sheet. I think that in the macro that contains the Select Case statement, you are working with, or have set, an ActiveSheet.

[vba]Sub DC()
'
' DC Macro
'

Sheets("Sheet3").Range("C2:H8").Copy

Sheets("Sheet1").Range("C6:H12").PasteSpecial Paste:=xlPasteColumnWidths

'If you don't need these attributes, you (usually) don't need to set them
'Operation:=xlNone, _
'SkipBlanks:=False, Transpose:=False

'Paste Sheets("Sheet3").Range("C2:H8") into the ActiveSheet.Range("A1:F7"),
'or down and right from any Cell that might be selected on the Activesheet, wherever that is.
'This might trigger the Validation macro again.
ActiveSheet.Paste 'Which sheet is active right now?'

Application.CutCopyMode = False
Range("C6").Select 'Uhhh, Why?

End Sub[/vba] When you edit a recorded Macro, one of the first things to do is replace all the ".(dot)Select (CR)Selection.(dot)" With a single .(dot)

[mil_tech]

Welcome to the forum, Mil Tech.

I clean up your macro code, but I did not change it's flow. I suspect that the problem is with the ActiveSheet.Paste line. Sheet.Select does not activate a sheet. I think that in the macro that contains the Select Case statement, you are working with, or have set an ActiveSheet.

[vba]Sub DC()
'
' DC Macro
'
'
Sheets("Sheet3").Copy
Sheets("Sheet1").Range("C6:H12").PasteSpecial Paste:=xlPasteColumnWidths
'Operation:=xlNone, _ If you don't need these attributes, you (usually) don't need to set them
'SkipBlanks:=False, Transpose:=False

ActiveSheet.Paste 'Which sheet is active right now?' Also error here, did not apply a range to paste to.
Application.CutCopyMode = False
Range("C6").Select 'Uhhh, Why?

End Sub[/vba]

Hi, I did itry to remove the active paste but it does not complete the paste, the screen stays blank, when I put it back it works. Is their another paste statement I can try?

[SamT]

Oops!
I edited my original post while you were answering it.

[SamT]

Mil Tech,

I am answering your post #3

When you have to work with an activesheet, it is probably the one you ran the first macro form. The first thing to do is set a variable to the active sheet.

In the macro that the user calls.
[vba]Dim WkSht As Worksheet
Set WkSht = ActiveSheet
.
.
.
Case "DC OFFICE"
DC WkSht[/vba]
In Sub DC
[vba]Sub DC(WkSht As Worksheet()
.
.
.
WkSht.Paste[/vba]
Note that I declared WkSht in the first macro and in the Sub DC arguments. This takes advantage of the Scope of Variables. The Scope of the WkSht declared in the first Macro is only within the first Macro. The Scope of the WkSht declare in DC's agrguments is only within Sub DC.

I could have used
[vba]Dim KISS As Worksheet
Set KISS = ActiveSheet
.
.
.
Case "DC OFFICE"
DC KISSt[/vba]
[vba]Sub DC(SayWhat As Worksheet)
.
.
.
SayWhat.Paste[/vba] And it would work exactly the same

[mdmackillop]

Better to paste to a single cell. Less risk of error.
[VBA]Sub DC()
Dim c As Range
Set c = Selection
Sheets("Sheet3").Range("C2:H8").Copy
With Sheets("Sheet1")
.Range("C6").PasteSpecial Paste:=xlPasteColumnWidths
.Paste
End With
Application.CutCopyMode = False
Application.Goto c
End Sub[/VBA]

[mil_tech]

I created a macro using this syntax and it does the samething, it just loops through the case statements and it never stops until it errors out or excel crashes. For some reason it appears that the case statements are not working the way they should. My understanding was that once a condition is met the script runs and it should terminate. This thing goes through the entire list and start over again cutting and pasting until I assume the buffer runsout. Any ideas?
I have included the file for your review.

Better to paste to a single cell. Less risk of error.
[vba]Sub DC()
Dim c As Range
Set c = Selection
Sheets("Sheet3").Range("C2:H8").Copy
With Sheets("Sheet1")
.Range("C6").PasteSpecial Paste:=xlPasteColumnWidths
.Paste
End With
Application.CutCopyMode = False
Application.Goto c
End Sub[/vba]

[SamT]

Mil Tech,

SheetChange EventOccurs when cells in any worksheet are changed by the user or by an external link.

This sub is triggers every time a paste event occurs anywhere in the workbook.
[vba]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Range("C4")
Case "DC OFFICE"
DC
Case "LA OFFICE"
.
.
.
Case Else
'Handle Errors in Case Else
End Select
End Sub[/vba]
Try the attached workbook.

Sheets("Sheet3") code
[vba]Option Explicit
'Option Explicit helps trap errors as you type

Private Sub Worksheet_Change(ByVal Target As Range)

'Some error trapping. Uncomment code lines as desired
'You MUST uncomment one set of the error traps

'Set 1: Don't run if more than one cell changes AND that cell is not "C3"
'If Target.Count <> 1 Then Exit Sub
'If Target.Address <> "C3" Then Exit Sub

'Set 2: Don't run unless "C3" is one of many changed cells
'If Not Intersect(Target, Range("C3")) Then Exit Sub

SelectOffice Target.Value

End Sub
[/vba] Module1 Code (Sample)
[vba]Option Explicit
'Option Explicit helps find errors as you type

Private Sub SelectOffice(WhichOffice As String)
Select Case WhichOffice
Case "DC OFFICE"
DC
Case "LA OFFICE"
MsgBox "This is LA OFFCE Message"
Case "NY OFFICE"
MsgBox "This is NY OFFCE Message"
Case "LN OFFICE"
MsgBox "This is LN OFFCE Message"
Case Else
'Handle errors in "Case Else"
End Select
End Sub

Sub DC()
'Bad Practice, you never really know which selection will be used.
'Every sheet has a selected range.
'Dim c As Range
'Set c = Selection

Sheets("Sheet3").Range("C2:H8").Copy
With Sheets("Sheet1")
.Range("C6").PasteSpecial Paste:=xlPasteColumnWidths
.Paste
End With
Application.CutCopyMode = False

'See Code in ThisWorkbook Code Pane
Application.Goto SelectedSheet.SelectedRange
End Sub[/vba]ThisWorkbook Code
[vba]Option Explicit

'Global Variables for use in Module 1
'"Remembers" the last selection made in the entire workbook.
Public SelectedSheet As Worksheet
Public SelectedRange As Range

Private Sub Workbook_SheetSelectionChange(ByVal WkSht As Object, _
ByVal Target As Excel.Range)

Set SelectedSheet = WkSht
Set SelectedRange = Target
End Sub[/vba]

[SamT]

Here's the attachment

[snb]

Probably this suffices:

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
Application.EnableEvents = False
Sheets("sheet1").Columns(1).Find(Target, , xlValues, xlWhole).Offset(, 2).Resize(7, 6).Copy Target.Offset(2)
Application.EnableEvents = True
End If
End Sub
[/vba]

NB. avoid merged cells in combination with VBA.

[mil_tech]

Here's the attachment

Thanks for your time Sam, but the spread sheet still does not work,and there isno longer a Data Validation Dropdown to select location, when I type thelocation in C4 it errors out. What I need is simple, the original macros Icreated worked, but when I called the macro in the Data Validation list itcontinually runs and does not stop until Excel errors out or crashes. Is theresomething wrong here? My understanding is that when Case Select is used itshould stop running the first Case match it finds correct?

[mdmackillop]

[VBA]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo Exits
Application.EnableEvents = False

Select Case Range("C4")

Case "DC OFFICE"
DC
Case "LA OFFICE"
MsgBox "This is LA OFFCE Message"
Case "NY OFFICE"
MsgBox "This is NY OFFCE Message"
Case "LN OFFICE"
MsgBox "This is LN OFFCE Message"

End Select

Exits:
Application.EnableEvents = True


End Sub
[/VBA]

[SamT]

Mil Tech,

SheetChange EventOccurs when cells in any worksheet are changed by the user or by an external link.

This sub You have in ThisWorkbook is triggered every time you paste something anywhere in the workbook.

[VBA]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Range("C4")
Case "DC OFFICE" DC
Case "LA OFFICE"
.
.
.
Case Else
'Handle Errors in Case Else
End Select
End Sub[/VBA]

[SamT]

Mil Tech,

This time I tested it before uploading. Good thing, too. I had to change a bunch of range addresses from your example to real workbook range addresses to make it work.

I also took out the code from Worksheet 1 and Un-Merged the Office range on sheet 3. Instead I used Format Cells >> Horizontal Alignment >> Center Across Selection to make it look the same as.

The attached works fine, no extra looping , copy gets the right range and pastes all go where they should. It only works on DC, LA, NY, and MU offices, of course.

Bye-Bye.

[mil_tech]

Mil Tech,



This sub You have in ThisWorkbook is triggered every time you paste something anywhere in the workbook.

[vba]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) [/vba][vba]

Select Case Range("C4")

Case "DC OFFICE" DC
Case "LA OFFICE"
.
.
.
Case Else
'Handle Errors in Case Else
End Select

End Sub

[/vba]

That appears to have done the trick, thanks so much everyone for your assistance on this. I need to get a good beginners book on VBA for Excel. You guys are the best be well.