Move copany 2 data to new sheet excel VBA

[udarawic]

My report comes with two company data and when Cell c data say "Company2" I need to move that row and all the rows below to sheet 2.

Can someone help me?

 

[udarawic]

https://www.dropbox.com/s/hg2girtxr4leytp/Provision_Code testing.xlsm?dl=0

Thx

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 ?

 

[Michael M]

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 !

 

[udarawic]

I am clicking the command button on ETL sheet. So the sheet reference is required...
I fix the issue with space. But still not copying the data...

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 !

 

[Michael M]

You also need to read here, as you are using ActiveX controls

Office Update breaks ActiveX controls | Excel Matters

 

[udarawic]

Yep.

I use lot of active x control and I am aware of this. All of my other activex controls are working perfectly.

Thx.
Isu

You also need to read here, as you are using ActiveX controls

Office Update breaks ActiveX controls | Excel Matters

 

[Michael M]

Ok, this is a bit desperate, but try

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

 

[udarawic]

Finally Yes...

Ok, this is a bit desperate, but try

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

 

[Michael M]

......

 

[udarawic]

Thx a lot mate.

......