Copy data from one sheet to another when criteria met in column

[born2program]

I need some direction on writing VBA code that will do the following in Excel. When I put a value in column 2 on sheet1, If the value = "A" then have it show on sheet 2.

Thanks.

 

[Loomah]

as both skip and myself have pointed out in the office forum you'll need to add a little more detail about what you are trying to achieve (and what you have tried so far)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[born2program]

I haven't really tried much since I've never really done any excel VBA programming. I usually program in asp.net etc. What I have is a sheet with an ALL tab and then tabs for individual departments. In the ALL tab if I put a department name in column 2 lets say department is "A" then I want it to show on the Department A tab, if "B" then show on the Department B tab and so on.

 

[born2program]

I did try a statement like the one below in the cells on Department A tab but it will bring the data over at the exact row location as it is on the ALL tab.

=IF(All!$D9="DeptA",All!H9,"")

 

[SkipVought]

As I posted in forum68, the objective of limiting the view to a Dept or all Dept's beginning the a certain letter, can easily be accomplished ON ONE SHEET, using the AutoFilter.

It is seldom a good practice to spawn similar data over multiple tables/sheets. There is a hidden overhead of space and maintenance that is not apparent to novice spreadsheet users.

AutoFilter is one method. Depending on other objectives, a PivotTable with Dept in the PAGE FIELD, migh be a possibility. You could also use MS Query with a Parameter query. The two latter options would put your data results on a second sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[born2program]

Thanks for the Help. I was able to do this with VBA.

 

[SkipVought]

b2p,

It is both customary and polite to post your solution, rather than merely stating, "I was able to do this with VBA." since many Tek-Tip members browse these threads looking for helpful specific information.

Please explain exactly HOW you were able to do 'this', whatever 'this' is, with VBA. So far, you have shared a disappointingly meager amount of information.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[born2program]

Didn't think about posting the solution. The code I used is below.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim nCol As Integer
  Dim nRow As Integer
  Dim Dest_row As Integer
  Dim i As Integer
  i = 4
  Dim iloop As Integer
  iloop = 0
  Dim DEST_BLO As Worksheet
  Set DEST_BLO = Worksheets("A")
  Dim All_WS As Worksheet
  Set All_WS = Worksheets("All")
  
  
  
   
  If Target.Column = 4 Then
    ThisRow = Target.Row
    
      DEST_BLO.Range("A2", "S1000").ClearContents
      
      nCol = 7
      nRow = 9
      Dest_row = 2
      While Not IsEmpty(Target.Cells(nRow, nCol))
        
        If All_WS.Cells(nRow, 4) = "A" Then
        
            DEST_BLO.Range("A" & Dest_row).Value = All_WS.Range("I" & nRow).Value
            DEST_BLO.Range("B" & Dest_row).Value = All_WS.Range("N" & nRow).Value
            DEST_BLO.Range("D" & Dest_row).Value = All_WS.Range("P" & nRow).Value
            
            DEST_BLO.Range("E" & Dest_row).Value = All_WS.Range("O" & nRow).Value
            
            
            
            If Len(All_WS.Range("V" & nRow).Value) > 0 Then
                DEST_BLO.Range("F" & Dest_row).Value = All_WS.Range("V" & nRow).Value
            Else
                DEST_BLO.Range("F" & Dest_row).Value = "0"
            End If
            
                        
                        
            If Len(All_WS.Range("G" & nRow).Value) > 0 Then
                DEST_BLO.Range("G" & Dest_row).Value = All_WS.Range("G" & nRow).Value
            Else
                DEST_BLO.Range("G" & Dest_row).Value = "0"
            End If
            
            
            DEST_BLO.Range("H" & Dest_row).Value = All_WS.Range("S" & nRow).Value

            
            If Len(All_WS.Range("AD" & nRow).Value) > 0 Then
                DEST_BLO.Range("I" & Dest_row).Value = All_WS.Range("AD" & nRow).Value
            Else
                DEST_BLO.Range("I" & Dest_row).Value = "0"
            End If
            
            
            If Len(All_WS.Range("AE" & nRow).Value) > 0 Then
                DEST_BLO.Range("J" & Dest_row).Value = All_WS.Range("AE" & nRow).Value
            Else
                DEST_BLO.Range("J" & Dest_row).Value = "0"
            End If
            
            
            If Len(All_WS.Range("AA" & nRow).Value) > 0 Then
                DEST_BLO.Range("K" & Dest_row).Value = All_WS.Range("AA" & nRow).Value
            Else
                DEST_BLO.Range("K" & Dest_row).Value = "0"
            End If
            
            
            If Len(All_WS.Range("J" & nRow).Value) > 0 Then
                DEST_BLO.Range("L" & Dest_row).Value = All_WS.Range("J" & nRow).Value
            Else
                DEST_BLO.Range("L" & Dest_row).Value = "0"
            End If
            
            If Len(All_WS.Range("W" & nRow).Value) > 0 Then
                DEST_BLO.Range("M" & Dest_row).Value = All_WS.Range("W" & nRow).Value
            Else
                DEST_BLO.Range("M" & Dest_row).Value = "0"
            End If
            
            If Len(All_WS.Range("Y" & nRow).Value) > 0 Then
                DEST_BLO.Range("N" & Dest_row).Value = All_WS.Range("Y" & nRow).Value
            Else
                DEST_BLO.Range("N" & Dest_row).Value = "0"
            End If
            
            
            If Len(All_WS.Range("E" & nRow).Value) > 0 Then
                DEST_BLO.Range("O" & Dest_row).Value = All_WS.Range("E" & nRow).Value
            Else
                DEST_BLO.Range("O" & Dest_row).Value = "0"
            End If
            
            If All_WS.Range("F" & nRow).Value <> "" Then
                DEST_BLO.Range("P" & Dest_row).Value = All_WS.Range("F" & nRow).Value
            End If
            
            'DEST_BLO.Range("P" & Dest_row).Value = All_WS.Range("F" & nRow).Value
                        
            
            Dest_row = Dest_row + 1
        
        
        End If
        nRow = nRow + 1
      Wend
    
  End If
  
    
End Sub

 

[SkipVought]

This is REALLY NOT an explanation!

We do not know what sheet the Change Event is on.

You have not explained what this process is supposed to do.

You did not explain what 'aha!' epiphany enlightened you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}