I am just trying to pull the data from one excel to another excel based on column name. Source excel name is "iTerm Export.xls". Sheet name is "export(1)". column heading is "Asset". so when run the below macro Asset column datas has to be copy and paste into the other excel i.e("iTerm metrics Report.xlsx")
But my issue if there is a blank cell anywhere in the Asset column, Ex: there are 50 data rows in Asset column. But 25th and 30 is a blank cell. when i am running the macro once 24 rows copy and paste in the other excel. but i need all fifty rows has to be copy and paste include blank row in other excel
Windows("iTerm Export.xls").Activate
Sheets("export(1)").Select
Cells.Find(What:="Asset", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("iTerm metrics Report.xlsx").Activate
Sheets("Raw Data from iTerm").Select
Range("A2").Select
ActiveSheet.Paste
Please help me on this.
Thanks
Arul
I won't suggest you to use .Select
as it is a major cause of errors. See this thread for example
Run Time Error '1004': Select method of Range Class failed VBA 2003
Having said that, I would recommend directly performing the action that you want rather than doing a .Select
first. Also how are you opening the workbooks "iTerm Export.xls" and "iTerm metrics Report.xlsx"? If they are already opened when you are running the macro then it's ok to use .Activate
else set a workbook variable and then open the workbooks. That ways you can avoid using .Activate
as well. Let me know if this is the case and I will provide a sample.
The other drawback of .Select
and .Activate
is that it slows down your code drastically.
Your above code can also be written as below. This is the correct way to use .Find
instead of directly using .Activate
. The reason being the code will crash on the below line if no match is found.
Cells.Find(What:="Asset", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, _ SearchFormat:=False).Activate
Hence it is advisable to check if the the value was found and then proceed.
Try this code and see if this is what you want? (UNTESTED)
Sub Sample()
Dim aCell As Range
Windows("iTerm Export.xls").Activate
With Sheets("export(1)")
Set aCell = .Cells.Find(What:="Asset", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
'~~> Check if "Asset is found
If Not aCell Is Nothing Then
'~~> get the lastrow of the column which has "Asset"
lastRow = .Range(Split(Cells(, aCell.Column).Address, "$")(1) & _
.Rows.Count).End(xlUp).Row
Windows("iTerm metrics Report.xlsx").Activate
.Range( _
Split(Cells(, aCell.Column).Address, "$")(1) & aCell.Row & _
":" & _
Split(Cells(, aCell.Column).Address, "$")(1) & lastRow _
).Copy _
Sheets("Raw Data from iTerm").Range("A2")
Else
MsgBox "Asset not found"
End If
End With
End Sub
HTH
Sid
To get the last row of your column you can do this instead:
lastRow = Selection.EntireColumn.Find(What:="*", after:=Range("A1"), _
LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).EntireRow.Row 'Use EntireRow to take hidden rows too
Then you can:
Range(Selection, Cells(lastRow, Selection.Column)).Select
It stops at a blank because thats what the xlDown selection does.
What I would suggest is selecting the entire column.
Columns(4).Select
or
Columns("D:D").Select
or, to get the column that has the active cell
Columns(ActiveCell.Column).Select
Sub Create_From_List()
Dim Row As Integer
Row = ActiveCell.Row
Dim objList As Worksheet
Set objList = ActiveSheet
Dim wb As Workbook
workingPath = ActiveWorkbook.Path
'insert the doc you want to transfer it to (and save it in the same folder as the one the source excel is)
Set wb = Workbooks.Add(workingPath & "\---")
' enter cells you copy from the source, then the name of the sheet of the target one and the cells you want to place the info in the target one.
CheckAndCopyData objList.Cells(- , -), wb.Sheets("---").Range("--")
CheckAndCopyData objList.Cells(- , -), wb.Sheets("---").Range("--")
CheckAndCopyData objList.Cells(- , -), wb.Sheets("---").Range("--")
CheckAndCopyData objList.Cells(- , -), wb.Sheets("---").Range("--")
CheckAndCopyData objList.Cells(- , -), wb.Sheets("---").Range("--")
CheckAndCopyData objList.Cells(- , -), wb.Sheets("---").Range("--")
Application.DisplayAlerts = False
' you can use the info that you transfer as a name of the doc (use cells of the source doc)
wb.SaveAs (workingPath & "\" & objList.Cells(- , -) & " " & objList.Cells(- , -) &
".xlsx")
Application.DisplayAlerts = True
End Sub
Function CheckAndCopyData(SourceCell As Range, TargetCell As Range)
If Not IsEmpty(SourceCell) Then
TargetCell.Value = SourceCell.Value
End If
End Function