Running into problems
Posted by Lori D on September 14, 2001 12:41 PM
Barrie,
Since I posted my original message, my main spreadsheet has grown. It is now complete, though as far as required fields. I tried pasting the macro you gave me into the spreadsheet and manipulating the cell addresses to fit the spreadsheet now, and I keep getting different errors.
The main spreadsheet has these columns:
A)Store B)Weight C)Cube D)Date
E,F,G) Some comparison data I,I,J,K)Hidden columns
How can I get this code right?
Posted by Barrie Davidson on September 14, 2001 1:00 PM
Lori, I've adjusted the code to compensate for your additional data (but I haven't tested it).
Sub Data_Copy_To_File()
' Written by Barrie Davidson
Dim searchValue
Dim searchValueAddress As String
Dim dataFile As String
Dim newFile As String
Application.ScreenUpdating = False
dataFile = ActiveWorkbook.Name
Range("A1", Range("A1").End(xlDown).Address).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range( _
"AA1"), Unique:=True
searchValueAddress = "AA2"
searchValue = Range("AA2").Value
Do Until searchValue = ""
Selection.AutoFilter
Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).Select
Selection.AutoFilter Field:=1, Criteria1:=searchValue
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Open FileName:="C:\Cube Analysis\Store" & searchValue & ".xls"
If Range("A1").Value <> "" Then
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.EntireRow.Delete
Else
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Range("A1").Select
ActiveWorkbook.Close (True)
Windows(dataFile).Activate
searchValueAddress = Range(searchValueAddress).Offset(1, 0).Address
searchValue = Range(searchValueAddress).Value
Loop
Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).AutoFilter
Range("AA1", searchValueAddress).ClearContents
Application.ScreenUpdating = True
Range("A1").Select
MsgBox ("Copying complete")
End Sub
Boy, we're almost becoming drinking buddies :)
Regards,
BarrieBarrie Davidson
Posted by LoriD on September 17, 2001 6:20 AM
Barrie,
I have to tell you...That was the first good chuckle I've had in about a week! Thanks. And your site is awesome.
Lori
