Macro or VBA Code for collating data

[Amit Srivastava]

Hi

I have a task of collating data from 20 or 22 text files in excel however, 99%of time I will have data in text delimited format but 1%chance that out of the 20odd text files 1 file will have the data formatted using text to column function when u test check it while opening the text files in excel. My query is, is there a way to build a macro which checks the data before collating everything in one sheet and exclude that 1 file where the data is already formatted using text to column. Unable to attach the screen shots of the data files. Please let me know if u need them I will mail them separately
Please help.

I already have a macro ( got it from one of the forums pasted below) to collate the data
however, in the last month we encountered a one off situation where the data
was in a different format( it was already formatted with rows and columns ) so
I want to build a check that while collating the data it excludes such one
off's which can then added manually in the collated data

I am not an expert in VBA coding or Macros so please guide me

Amit Srivastava

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A1000000").End(xlUp)(2)
Next
End Sub

 

[EFANYoutube]

From what i understand you want to copy only the sheets where the data is in delimited form, this means that data only shows up in the first column, so i just put a quick check in for you, it checks the second column second row(incase you have headers) and if there is text in there it will tell you at the end and not copy that sheet
Sub Combine()
Dim J As Integer, ErrSheets As String
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
If Cells(2, 2).Value <> "" Then
If ErrSheets = "" Then ErrSheets = ActiveSheet.Name
If ErrSheets = "" Then ErrSheets = ErrSheets & ":" & ActiveSheet.Name
GoTo Next1
End If
Selection.CurrentRegion.Select ' select all cells in this sheets

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A1000000").End(xlUp)(2)
Next1:
Next
If ErrSheets <> "" Then MsgBox "there were errors in sheets: " & ErrSheets
End Sub

 

[Amit Srivastava]

Thanks EfanYoutube I will try the code u have suggested and will update u with the results

Many thanks once again for helping out

 

[Amit Srivastava]

thanks EfanYoutube it worked wonderfully ....