Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Apr 16th, 2012  |  syntax: None  |  size: 1.82 KB  |  hits: 15  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. copy and paste the data one excel to another excel based on column name include blank cells
  2. Windows("iTerm Export.xls").Activate
  3. Sheets("export(1)").Select
  4. Cells.Find(What:="Asset", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  5. :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  6. False, SearchFormat:=False).Activate
  7. Range(Selection, Selection.End(xlDown)).Select
  8. Selection.Copy
  9. Windows("iTerm metrics Report.xlsx").Activate
  10. Sheets("Raw Data from iTerm").Select
  11. Range("A2").Select
  12. ActiveSheet.Paste
  13.        
  14. Sub Sample()
  15.     Dim aCell As Range
  16.  
  17.     Windows("iTerm Export.xls").Activate
  18.  
  19.     With Sheets("export(1)")
  20.         Set aCell = .Cells.Find(What:="Asset", LookIn:=xlFormulas, LookAt _
  21.         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  22.         False, SearchFormat:=False)
  23.  
  24.         '~~> Check if "Asset is found
  25.         If Not aCell Is Nothing Then
  26.             '~~> get the lastrow of the column which has "Asset"
  27.             lastRow = .Range(Split(Cells(, aCell.Column).Address, "$")(1) & _
  28.             .Rows.Count).End(xlUp).Row
  29.  
  30.             Windows("iTerm metrics Report.xlsx").Activate
  31.  
  32.             .Range( _
  33.             Split(Cells(, aCell.Column).Address, "$")(1) & aCell.Row & _
  34.             ":" & _
  35.             Split(Cells(, aCell.Column).Address, "$")(1) & lastRow _
  36.             ).Copy _
  37.             Sheets("Raw Data from iTerm").Range("A2")
  38.         Else
  39.             MsgBox "Asset not found"
  40.         End If
  41.     End With
  42. End Sub
  43.        
  44. Columns(4).Select
  45.        
  46. Columns("D:D").Select
  47.        
  48. Columns(ActiveCell.Column).Select
  49.        
  50. lastRow = Selection.EntireColumn.Find(What:="*", after:=Range("A1"), _
  51.         LookIn:=xlFormulas, SearchOrder:=xlByRows, _
  52.         SearchDirection:=xlPrevious).EntireRow.Row 'Use EntireRow to take hidden rows too
  53.        
  54. Range(Selection, Cells(lastRow, Selection.Column)).Select
create a new version of this paste RAW Paste Data