Move copany 2 data to new sheet excel VBA

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,761
Office Version
2013
Platform
Windows
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

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

Well-known Member
Joined
Oct 27, 2005
Messages
18,761
Office Version
2013
Platform
Windows
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
 

udarawic

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

Watch MrExcel Video

Forum statistics

Threads
1,095,548
Messages
5,445,123
Members
405,317
Latest member
gcallaway

This Week's Hot Topics

Top