Move copany 2 data to new sheet excel VBA

udarawic

New Member
Joined
Dec 29, 2014
Messages
29
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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 !
 
Upvote 0
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 !
 
Upvote 0
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
 
Upvote 0
Finally Yes...


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
 
Upvote 0

Forum statistics

Threads
1,212,947
Messages
6,110,837
Members
448,302
Latest member
sniffit1st

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top