VBA Excel: Move rows which match a certain feature between tabs

[Alex]

Hi,

I have a hughe matrix of numbers. Such matrix has headers in its
columns (first row). Say that headers are A, B and C ().

What I want to do with a VBA script is to look into the rows to check
if what of them have the value "30" in the column header.

In such case, I would like to move the row from that tab to another
one called, e.g., "Tab_30". An important point is that I need that the
row in the original matrix has to be deleted. I do not like blank rows
in the original matrix nor the new matrix in the tab "Tab_30".

I am not an expert in Excel. I just record some macros in the
spreadsheet and then I modify them in VB.

Thanks in advance.

 

[Bernard Liengme]

Just to be safe I used two steps: (1) copy & paste and (2) column delete
This code works on one sheet; you can modify if for your 98 sheets knowing
what names they have

Sub tryme()
Worksheets("Sheet1").Select
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
MsgBox mycell.Value
tabcount = tabcount + 1
mycell.Resize(mycell.End(xlDown).Row).Copy
Worksheets("Tab_30").Cells(1, tabcount)
End If
Next
For Each mycell In Range("A1:Z1")
If mycell.Value = 30 Then
mycell.EntireColumn.Delete
End If
Next
End Sub

best wishes

 

[Alex]

Hi Bernad:

Thank you very much for your response, it seems very good coded. But I
have a little problem. When I run the macro, it gives me an error:

Compile error:
Syntax error

and it highlights the 8th line, which is:

Worksheets("Tab_30").Cells(1, tabcount)

Could you have a look on it to check is there is something miss? I
can't do it, since I don't manage VBA .

One of my goals is to start studying VBA in Dec.

Best regards,

Alex

 

[Bernard Liengme]

Do you have an empty sheet named Tab_30 ready to receive the data?
I have just run my macro again and it works fine,
Send me a private message (remove TRUENORTH) and I will send you the
workbook
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Hi Bernad:

Thank you very much for your response, it seems very good coded. But I
have a little problem. When I run the macro, it gives me an error:

Compile error:
Syntax error

and it highlights the 8th line, which is:

Worksheets("Tab_30").Cells(1, tabcount)

Could you have a look on it to check is there is something miss? I
can't do it, since I don't manage VBA .

One of my goals is to start studying VBA in Dec.

Best regards,

Alex