Ginny
02-05-2007, 09:54 AM
I have been using a macro (written by someone I no longer have contact with) each day for years on spreadsheets I receive from a client . The client is updating their ordering system, so I have to take the order info and place each order into a spreadsheet in the same format that we used to receive it so I can still use the macro. The the problem I am having with this new spreadsheet is that the macro is supposed to look for each specific item (product) in a column, and then if found, select that entire row and paste that entire row into a new .txt file for that item, which will contain all orders found for that item in the spreadsheet. The problem is that the resulting .txt file has rows containing some info from other rows than the one selected! I thought it may be may be a compatibility issue because the orginal files and/or macro may be in 5.0/95, because when I try to change something in the file and then save, it asks me if I want to keep it in that format. But, I can take one of the orginal client files, and do a "save as" a new file and it still works. The current version of excel is 2003. Below is the code for that section of the macro.
Any ideas?
LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Look for requests for B Letterhead
BLH = False
For i = 1 To LastRow
Select Case Cells(i, 1)
Case Is = "PLH27"
BLH = True
End Select
Next i
' If found export them to a B Letterhead worksheet
If BLH = True Then
n = 1
' Add a worksheet and call it newsheet
Worksheets.Add
ActiveSheet.Name = "newsheet"
Sheets("sheet1").Select
' Find the B Letterheads
For i = 1 To LastRow
Select Case Cells(i, 1)
Case Is = "PLH27"
' Copy it to the worksheet
Cells(i, 1).Activate
ActiveCell.EntireRow.Select
Selection.Cut
Sheets("newsheet").Select
ActiveSheet.Cells(n, 1).Activate
ActiveSheet.Paste
Sheets("sheet1").Select
Cells(i, 1).Activate
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
' Increment our counters
i = i - 1
LastRow = LastRow - 1
n = n + 1
End Select
Next i
' Now write the B Letterhead file
Sheets("newsheet").Select
ChDir (MyPath)
If Mac = True Then
ActiveWorkbook.SaveAs Filename:= _
MyPath & ":B_LH " & DT & ".txt" _
, FileFormat:=xlText, CreateBackup:=False
Else:
ActiveWorkbook.SaveAs Filename:= _
MyPath & "\B_LH " & DT & ".txt" _
, FileFormat:=xlText, CreateBackup:=False
End If
ActiveWindow.SelectedSheets.Delete
Sheets("sheet1").Select
End If
Any ideas?
LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Look for requests for B Letterhead
BLH = False
For i = 1 To LastRow
Select Case Cells(i, 1)
Case Is = "PLH27"
BLH = True
End Select
Next i
' If found export them to a B Letterhead worksheet
If BLH = True Then
n = 1
' Add a worksheet and call it newsheet
Worksheets.Add
ActiveSheet.Name = "newsheet"
Sheets("sheet1").Select
' Find the B Letterheads
For i = 1 To LastRow
Select Case Cells(i, 1)
Case Is = "PLH27"
' Copy it to the worksheet
Cells(i, 1).Activate
ActiveCell.EntireRow.Select
Selection.Cut
Sheets("newsheet").Select
ActiveSheet.Cells(n, 1).Activate
ActiveSheet.Paste
Sheets("sheet1").Select
Cells(i, 1).Activate
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
' Increment our counters
i = i - 1
LastRow = LastRow - 1
n = n + 1
End Select
Next i
' Now write the B Letterhead file
Sheets("newsheet").Select
ChDir (MyPath)
If Mac = True Then
ActiveWorkbook.SaveAs Filename:= _
MyPath & ":B_LH " & DT & ".txt" _
, FileFormat:=xlText, CreateBackup:=False
Else:
ActiveWorkbook.SaveAs Filename:= _
MyPath & "\B_LH " & DT & ".txt" _
, FileFormat:=xlText, CreateBackup:=False
End If
ActiveWindow.SelectedSheets.Delete
Sheets("sheet1").Select
End If