nickshep85
New Member
- Joined
- Mar 21, 2012
- Messages
- 37
Hi all,
I have a macro which I am working on that imports a large text file into Excel. This text file then needs to be separated into two sheets "Tyres" and "Mechanical". I have got so far as to import the file, then show which rows need to be transferred to which sheets, but am stuck on how I would do this. Please can someone put me out of my misery?
The file "PriceFile" is my imported text file which, depending on the size of the file, may be over two or more sheets. Column I in each sheet has a formula which gives a TRUE / FALSE result. For this, rows with TRUE values need to be transferred into the "Mechanical" sheet in "ImportFile". Rows with FALSE values need to be transferred into the "Tyres" sheet.
The data in the below example would be transferred into the Mechanical Sheet, my PriceFile workbook uses "XXX" just as column headers.
[TABLE="width: 576"]
<tbody>[TR]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[/TR]
[TR]
[TD="align: center"]P122XZY08M[/TD]
[TD="align: center"]12-22.5 XZY P/W 08MM[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"]XX[/TD]
[TD="align: center"]R24[/TD]
[TD="align: center"]116.05[/TD]
[TD="align: center"]116.96[/TD]
[TD="align: center"]VSV999[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
Any help in this would be much appreciated, a final solution even more so.
Thanks.
I have a macro which I am working on that imports a large text file into Excel. This text file then needs to be separated into two sheets "Tyres" and "Mechanical". I have got so far as to import the file, then show which rows need to be transferred to which sheets, but am stuck on how I would do this. Please can someone put me out of my misery?
Code:
Sub Transfer_Data()
'Add New Workbook
Workbooks.Add template:=xlWorksheet
Dim mypath As String
mypath = ThisWorkbook.Path
'Add New Sheet and Overwrite Last File
ActiveWorkbook.Sheets.Add
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (mypath & "/ImportFile.xls")
Application.DisplayAlerts = True
'Name Sheets
Sheets("Sheet1").Name = "Tyres"
Sheets("Sheet2").Name = "Mechanical"
'Add Title and Date into Both Sheets
Dim wksh As Worksheet
On Error Resume Next
For Each wksh In Worksheets
With wksh
.Range("A1").FormulaR1C1 = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-5)"
.Range("B1").FormulaR1C1 = "=today()"
.Range("B1").Select
.Range("B1").Copy
.Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Application.Goto Reference:="R1C1"
.Application.CutCopyMode = False
'Add Headers into both Sheets
.Range("A2").Value = "CODE"
.Range("B2").Value = "DESCRIPTION"
.Range("C2").Value = "XXX"
.Range("D2").Value = "XXX"
.Range("E2").Value = "XXX"
.Range("F2").Value = "XXX"
.Range("G2").Value = "PRICE"
.Range("H2").Value = "XXX"
.Range("I2").Value = "XXX"
End With
Next wksh
'Format Both Sheets
Sheets.Select
Range("A1:I2").Select
With Selection
.Font.Size = 14
.Font.Bold = True
.Font.Color = vbWhite
.Interior.Color = vbBlue
End With
Range("A1").Select
Sheets("Tyres").Activate
'Set LastRows for both sheets
Dim LastTyres As Long
LastTyres = Sheets("Tyres").Cells.Find(What:="*", _
searchdirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Sheets("Mechanical").Activate
Dim LastMech As Long
LastMech = Sheets("Mechanical").Cells.Find(What:="*", _
searchdirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'Copy TRUE Values from PriceFile into next available row in Mechanical
End SubThe file "PriceFile" is my imported text file which, depending on the size of the file, may be over two or more sheets. Column I in each sheet has a formula which gives a TRUE / FALSE result. For this, rows with TRUE values need to be transferred into the "Mechanical" sheet in "ImportFile". Rows with FALSE values need to be transferred into the "Tyres" sheet.
The data in the below example would be transferred into the Mechanical Sheet, my PriceFile workbook uses "XXX" just as column headers.
[TABLE="width: 576"]
<tbody>[TR]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[/TR]
[TR]
[TD="align: center"]P122XZY08M[/TD]
[TD="align: center"]12-22.5 XZY P/W 08MM[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"]XX[/TD]
[TD="align: center"]R24[/TD]
[TD="align: center"]116.05[/TD]
[TD="align: center"]116.96[/TD]
[TD="align: center"]VSV999[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
Any help in this would be much appreciated, a final solution even more so.
Thanks.
