If field has an X, copy cell to another sheet

Question

I have an Excel tracker that I put an "X" in a cell every month if a certain activity is accomplished.

This "X" correlates to a range of cells on the same sheet.

I want when I click on a command box;

If the cell for January has an "X" copy specific cells on the current page to specific cells on another work sheet.
If the cell for February has an "X" copy some other specific cells on the current page to some other specific cells on the other worksheet.

So on and so forth through December.

I have the following code (which does not work):

Private Sub CommandButton1_Click()
Sheets("MRT").Select
If InStr(1, (Range("L8").Value), "X") > 0 Then
    Range("E42:AA42").Select
    Selection.Copy
    Sheets("Test '12").Select
    Cells(3, AP).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
End If
End Sub

Can you be more specific on the specific cells? If January is L8 what is February? if the range to be copied against L8 is E42:AA42 what is February? What rules should be followed for the destination sheet name and position etc? — brettdj
this sounds really like a VLOOKUP, however one would have to use it in each of the copy-target-cells and would not have a button. — Jook

Jook Jook · Accepted Answer · 2012-09-14T07:27:07

Try this:

Private Sub CommandButton1_Click()
 If Sheets("MRT").Range("L8").Value like "*X*" Then

   Sheets("MRT").Range("E42:AA42").Copy
   Sheets("Test '12").Cells(3, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
 End If
End Sub

Worked in my test, however you might want to adapt Cells(3,1) and the other position specifiers to your desired targets.

edit: forgot about the part with the months ... wait a minute ... here:

Sub FindSignificant()
    Dim SearchString As String
    Dim SearchRange As Range, cl As Range
    Dim FirstFound As String
    Dim sh As Worksheet

    ' Set Search value
    SearchString = "a"
    Application.FindFormat.Clear
    ' loop through all sheets
        Set sh = Sheets("MRT")
        ' Find first instance on sheet
        Set cl = sh.Cells.Find(What:=SearchString, _
             After:=sh.Cells(1, 1), _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not cl Is Nothing Then
            ' if found, remember location
            FirstFound = cl.Address
            ' format found cell
            Do
                Select Case sh.Cells(cl.Row, 1).Value
                  Case "december"
                    sh.Range("E42:AA42").Copy
                    Sheets("Test '12").Cells(3, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
                  Case "february"
                    sh.Range("E42:AA42").Copy
                    Sheets("Test '12").Cells(3, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
                  Case Else
                    'do nothing
                End Select

                ' find next instance
                Set cl = sh.Cells.FindNext(After:=cl)
                ' repeat until back where we started
            Loop Until FirstFound = cl.Address
        End If
End Sub

this code origins from here

You would have to adapt the select case, but i really would think about solving this without VBA, if it is not necessary ;)

If field has an X, copy cell to another sheet

1 Answers