In that case can you either post a small sample here using the HTML Maker in my tag
OR
Upload the worksheet to Dropbox and post the link back here !
Also, are you placing the code in a standard module AND making the data sheet that needs to be copied the Activesheet ?
Two things to fix...
Put the code in a Standard module....not the Sheet module
The Uppercase text in Cell C34 won't be recognised in the macro because there is a trailing space in the cell !
You also need to read here, as you are using ActiveX controls
Office Update breaks ActiveX controls | Excel Matters
Private Sub CommandButton1_Click()
'''to get rid of all the merge cells in the complete sheet
'Sheets("ECA562 Aged Debt - Original").Select
'ActiveSheet.Cells.Select
' With Selection
' .Orientation = 0
' .AddIndent = False
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
' End With
Sheets("ECA562 Aged Debt - Original").Select
Dim lr As Long, r As Long
lr = Sheets("ECA562 Aged Debt - Original").Cells(Rows.Count, "C").End(xlUp).Row
'lr = Cells(Rows.Count, "C").End(xlUp).Row
For r = 2 To lr
If Sheets("ECA562 Aged Debt - Original").Range("C" & r).Value = "ENERGY TELECOMMUNICATIONS PTY LTD" Then
Sheets("ECA562 Aged Debt - Original").Rows(r & ":" & lr).Copy Destination:=Sheets("ECA562 - EETL").Range("A2")
Exit Sub
End If
Next r
End Sub
Ok, this is a bit desperate, but try
Code:Private Sub CommandButton1_Click() '''to get rid of all the merge cells in the complete sheet 'Sheets("ECA562 Aged Debt - Original").Select 'ActiveSheet.Cells.Select ' With Selection ' .Orientation = 0 ' .AddIndent = False ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With Sheets("ECA562 Aged Debt - Original").Select Dim lr As Long, r As Long lr = Sheets("ECA562 Aged Debt - Original").Cells(Rows.Count, "C").End(xlUp).Row 'lr = Cells(Rows.Count, "C").End(xlUp).Row For r = 2 To lr If Sheets("ECA562 Aged Debt - Original").Range("C" & r).Value = "ENERGY TELECOMMUNICATIONS PTY LTD" Then Sheets("ECA562 Aged Debt - Original").Rows(r & ":" & lr).Copy Destination:=Sheets("ECA562 - EETL").Range("A2") Exit Sub End If Next r End Sub