Conditional Copy of Data using the macro

[joshatse]

Hi,

I have a program which automatically propogates the data to the Excel Spreadsheet using the excel templates.

I would like to call the macro which is executed after the Data is propogated to the Excel Spreadsheet. How can I activate the macro automatically after the dataload ?

Assuming that the old1.xls is the spreadsheet after the data loading, I would like to search for the Value 50 in Column B and find all the row and insert them to the A2 row onwards as per the example in New1.xls. The rows should be shifted up automatically when the insert occurs.

Thanks a lot for your help.

[Simon Lloyd]

Well firstly the workbooks you supplied have no code whatsoever in them, so its impossible to give you the name of the macro but the convention is you use Call MacroName where MacroName is the actual name of the macro.

As for the rest of your question, it is possible as a simple IF THEN loop Copy EntireRow.....but you didnt say copy from where to which destination i.e workbook? worksheet?, and why would you want to insert rows? is there already data in the destination sheet?, rather than shift up do you not mean shift down or will the copied data just go at the end of any data that is already there?.

Regards,
Simon

[joshatse]

Hi Simon,

I didn't ask for the name. I would like to have the macro code, which can be used to look for all the 50 in Column B.

It should copy all the rows which matches the above search criteria.

All the matches rows are copied starting from Row 2. The Row 2 is located by searching for Malaysia as per the new1.xls.

Thank you.

Regards,
Mahesh

[joshatse]

You are right, I meant shift down.

[Simon Lloyd]

Try this it will work for the same page, it's not great but should do the trick!

Option Explicit
Sub Find_50_Move()
Dim Rng1, Mycell As Range
Dim i As Integer
Set Rng1 = Range("B3:B65536")
For Each Mycell In Rng1
If Mycell = "" Then Exit Sub
If Mycell.Value = 50 Then
Mycell.EntireRow.Cut
Range("A2").Insert Shift:=xlDown
End If
Next

End Sub
Regards,
Simon
P.S will only work for contiguous data!

[joshatse]

Hi Simon,

It seems to do the job except but that the records are inserted at the top so it's able to skip some of the last rows which match the search criteria.

The best solution would be select all the rows, where Coumn B contains 50, cut them and paste at A2.

I have tried to make the sample code, but still no luck.

Thank you.

Regards,
Mahesh

[Simon Lloyd]

in that case you need to put the rows addresses in to an array, insert the array at the desired location then delete the original addresses.

Thats a little beyond me at the moment

Regards,
Simon

[Bob Phillips]

Sub Find_50_Move()
Dim rng As Range
Dim oArea As Range
Dim oCell As Range
Dim iLastRow As Long

Dim i As Integer
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 4 To iLastRow
If Cells(i, "B").Value = 50 Then
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next

If Not rng Is Nothing Then
Set oCell = Range("A2")
For Each oArea In rng.Areas
oArea.Cut
oCell.EntireRow.Insert
Next oArea
End If

End Sub

[tstom]

I have a program which automatically propogates the data to the Excel Spreadsheet using the excel templates.

I would like to call the macro which is executed after the Data is propogated to the Excel Spreadsheet. How can I activate the macro automatically after the dataload ?

Please explain. DDE? A Web Query? If DDE, see the SetLinkOnData method of the workbook class to automate a response to an update. If a Web Query, see the events of the QueryTable object. Note that the SetLinkOnData method only works if you have a registered function within a cell. An OleLink type. In any regard, there are other good ways to respond to dynamic data updates but you will need to provide some neccesary details.

You could use the above to call Xld's "Sub Find_50_Move() "