Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row contents based on contents
I'm working on a macro to automagically copy the entire row of a worksheet
(Sheet 1) to Sheet 2 if the cell in column D contains the word "total". I got this to work once but then I took the code and tried using it for a different worksheet. Once I did this it no longer worked. There must be some minutia that I'm missing. Can someone please help me. Thank you. Here's the code I have so far: Sub CopyTotalsWithAutofilter() Dim FilterValue As String Dim rng As Range Dim rng2 As Range Set rng = Sheets("Sheet1").Range("D:D") FilterValue = "*total*" rng.AutoFilter Field:=1, Criteria1:=FilterValue With Sheets("Sheet1").AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng2 Is Nothing Then rng2.EntireRow.Copy Sheets("Sheet2").Range("A1") End If End With ActiveSheet.AutoFilterMode = False End Sub Thanks!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row contents based on contents
Your code is pretty specific in a couple places.
Did you change all the references to Sheet1 and sheet2 to what you needed for that other worksheet? In fact, you may want to remove the Worksheets("Sheet1") reference and just change it to activesheet. But I'm not sure how you could be so generic for the "receiving" worksheet name. naterator wrote: I'm working on a macro to automagically copy the entire row of a worksheet (Sheet 1) to Sheet 2 if the cell in column D contains the word "total". I got this to work once but then I took the code and tried using it for a different worksheet. Once I did this it no longer worked. There must be some minutia that I'm missing. Can someone please help me. Thank you. Here's the code I have so far: Sub CopyTotalsWithAutofilter() Dim FilterValue As String Dim rng As Range Dim rng2 As Range Set rng = Sheets("Sheet1").Range("D:D") FilterValue = "*total*" rng.AutoFilter Field:=1, Criteria1:=FilterValue With Sheets("Sheet1").AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng2 Is Nothing Then rng2.EntireRow.Copy Sheets("Sheet2").Range("A1") End If End With ActiveSheet.AutoFilterMode = False End Sub Thanks!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate contents of cells whose contents resemble cell referem | Excel Worksheet Functions | |||
Copy contents of one cell to another based on matching in 2 other | Excel Discussion (Misc queries) | |||
Copy contents to cell based on value in second cell | Excel Discussion (Misc queries) | |||
Copy contents of Cell to array based on Col1 value not null | Excel Programming | |||
Copy/paste based on adjacent cell contents | Excel Programming |