Help writing macro/VBA

Lori, can you be a little more specific. Which cell are you determining the value ( > 0)? Where is the data range to copy to?

Regards,
BarrieBarrie Davidson

OK, I'm tracking cube utilization of outbound trailers. Column A will be the store #, Column B & C contain the cube & weight for the trailer for that store. Each store gets at least a trailer a day, sometimes more. So I want any row containing 1 (Store 1) to copy to a another location ( or worksheet) so I can track store 1 independantly. Any row containing a 12 (store 12) to copy to it's own location and so on. Sorry for the lengthiness here.

Lori, as I understand it, your data consists of 3 columns (store number, cube, and weight) and you want to copy each store's record (or records if they have more than one trailer) to a new location. Is this correct? If yes, where are you planning to copy the information?

BarrieBarrie Davidson

That is correct Barrie. I'd like to copy this data to a separate spreadsheet.
Thanks (again)... Lori

Lori, are you copying the information to a new spreadsheet for each store or does each store have its own existing spreadsheet? And, does your data have headers?

BarrieBarrie Davidson

There will be an existing spreadsheet for each store, and I have column headings, if that's what you mean.

Where will the data be pasted in the existing spreadsheet?

BarrieBarrie Davidson

In the next available row. This spreadsheet is intended to track the data for store "x" only.

Lori

Lori, try this code out (make sure, of course, that you are using test spreadsheets and not the real thing when you try it).

Sub Data_Copy_To_File()
' Written by Barrie Davidson

Dim searchValue
Dim searchValueAddress As String
Dim dataFile As String
Dim newFile As String

dataFile = ActiveWorkbook.Name
Range("A1", Range("A1").End(xlDown).Address).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range( _
"E1"), Unique:=True
searchValueAddress = "E2"
searchValue = Range("E2").Value
Do Until searchValue = ""
Selection.AutoFilter
Range("A1", Range("C1").End(xlDown).Address).Select
Selection.AutoFilter Field:=1, Criteria1:=searchValue
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
MsgBox ("Select file for Store # " & searchValue)
newFile = Application.GetOpenFilename
Workbooks.Open FileName:=newFile
newFile = ActiveWorkbook.Name
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.EntireRow.Delete
Range("A1").Select
ActiveWorkbook.Close
Windows(dataFile).Activate
searchValueAddress = Range(searchValueAddress).Offset(1, 0).Address
searchValue = Range(searchValueAddress).Value
Loop
Range("A1", Range("C1").End(xlDown).Address).AutoFilter
Range("E1", searchValueAddress).ClearContents
Range("A1").Select
End Sub

Hope this works for you :)

Regards,
BarrieBarrie Davidson

Thanks Barrie,
This is such a great help to me...This runs fine up until I open the workbook for the store it wants. Then I get this error message: Run-time error '1004': Application-defined or object-defined error. I have no idea how to fix this. Do you have time to help me out just a little more on this? Greatly appreciative,
Lori

Lori, select the debug option at this point and tell me what line you are at when you go to the debug window.

Barrie
Barrie Davidson

Barrie
I am at this line: Range("A1").End(xlDown).Offset(1, 0).Select

Okay, it appears that you have nothing in the first cell of your Store's file. Change that line to read

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

This should do the trick!

BarrieBarrie Davidson

:

Thanks so much Barrie! This works perfectly.
Lori

Hi Lori & Barrie
I am trying to do something similar:
I am trying to build a sheet that will help me maximize the volume of a container.
I have several products (different sizes) and I am trying to build a tool that will tell me the quantity of boxes per type, that can fit (together) in to the container.
What I want to do, is punch in the number of boxes (per type) and have excel calculate for me (optimize) the number of boxes that will actually fit into the container.
I was wondering if Lori's workbook could help.
I also sort of lost the thread of correspondence between you two, so I can't even reproduce the full macro (after the last change from Barrie).
Lori, can you please send me a sample of your sheet (including the macro from Barrie)?
TIA
Dror