Locating Data ocurance in Mulltile Excel Files

[Vijay50]

Friends


I have few excel files containg stocks filtered based on difrent conditions. i would like like to locate and make a new file containing stock names appearing in diffrent sheets and no of occurances.

Your help will be very much appreciated


Thanks & Regards

Vijay

 

[Kiran Kumar]

Hi Vijay,


Welcome to the forum.


Here is the code to extract unique text from all the sheets ("A" Column)


Change the column names as your requirement.


You can do further followup for any queries. Am going for a movie now. Will come back and see the loop of queries and learn something.


Sub OneTimeValue()

Application.DisplayAlerts = False

Application.ScreenUpdating = False

For Each ws In Sheets

Application.DisplayAlerts = False

If ws.Name = "OneTimeValue" Then ws.Delete

Application.DisplayAlerts = True

Next

Set x = Sheets.Add(after:=Sheets(Sheets.Count))

x.Name = "Consolidated"

y = 1

For i = 1 To Sheets.Count - 1

r = Sheets(i).UsedRange.Rows.Count

Sheets(i).Range("A1:A" & r).Copy

Cells(y, "A").PasteSpecial xlValues

y = Sheets("Consolidated").UsedRange.Rows.Count + 1

Next


Set x = Sheets.Add(after:=Sheets(Sheets.Count))

x.Name = "OneTimeValue"


Sheets("Consolidated").Activate

Sheets("Consolidated").Cells(1, "B").Select


ActiveCell.FormulaR1C1 = "=COUNTIF(C1, RC[-1])"

Range("B1").Select

Selection.Copy

Range("A1").Select

Selection.End(xlDown).Select

ActiveCell.Offset(0, 1).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Selection.Copy

Range(Selection, Selection.End(xlUp)).Select

ActiveSheet.Paste

Range("A1").Select

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Application.CutCopyMode = False

Selection.AutoFilter

Range("B1").Select

Selection.AutoFilter Field:=2, Criteria1:="1"

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Sheets("OneTimeValue").Activate

ActiveSheet.Paste

Selection.End(xlUp).Select


Application.DisplayAlerts = False

Sheets("Consolidated").Activate

ActiveWindow.SelectedSheets.Delete


Application.DisplayAlerts = True

Sheets("OneTimeValue").Select

MsgBox "Report is Done", vbOKOnly + vbInformation

Application.ScreenUpdating = True

End Sub

 

[Vijay50]

Thank you Kiran. Hope you enjoyed the movie...


I will try this and report back with further questions, which I am sure I will have.


I am not so familiar with VBA rouintes, to start with it will be helpful it will be helpful if you can mark the lines or values I should modify as per my ws names, column etc..and any additional data i must add to successfully run this


Thank you once again..


Vijay

 

[Vijay50]

Hi Kiran


I get an error 1004 , cannot rename the sheet samas another one, on the following line.


I know this may be too silly to ask on this forum, can you send me your mail id to help me thorough with this macro.


I am planning join the VBA course soon.


x.Name = "Consolidated"


Regards

Vijay

 

[Kiran Kumar]

Hi Vijay,

Happy to see you again!


You can change the sheet names at any time, but ensure you do not have a sheet name with "Consolidated" & "OneTimeValue". As these two sheets will be used by the macros for you to get the output.


If you wish, you can draft me a test mail to exceltrackers@yahoo.com so that I can send u the file with an example.


About your VBA course - I have done the online macro training with Chandoo.org, personally I can say this is the right place we can learn excel and macros properly.


Rgds,

KK