MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help writing macro/VBA


Posted by Lori D on September 10, 2001 8:14 AM

Help! I don't know how to write anything in VB or a macro, but I need to know how (and if) there is a way to write a macro that tells Excel that if a record in a specific cell contains a value >0, copy that record to a data range of the same name in the 1st available row?


Posted by Barrie Davidson on September 10, 2001 11:59 AM

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

Posted by Lori D on September 12, 2001 5:28 AM

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.

Posted by Barrie Davidson on September 12, 2001 6:39 AM

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

Posted by Lori D on September 12, 2001 7:11 AM

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

Posted by Barrie Davidson on September 12, 2001 7:34 AM

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

Posted by LoriD on September 12, 2001 8:00 AM

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

Posted by Barrie Davidson on September 12, 2001 8:08 AM

Where will the data be pasted in the existing spreadsheet?

BarrieBarrie Davidson

Posted by Lori D on September 13, 2001 5:24 AM

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

Lori

Posted by Barrie Davidson on September 13, 2001 7:04 AM

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

Posted by Lori D on September 13, 2001 7:32 AM

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

Posted by Barrie Davidson on September 13, 2001 7:49 AM

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

Posted by Lori on September 13, 2001 8:10 AM


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

Posted by Barrie Davidson on September 13, 2001 8:29 AM

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

Posted by Lori on September 13, 2001 8:49 AM

:

Thanks so much Barrie! This works perfectly.
Lori

Posted by Dror Amitai on September 13, 2001 1:45 PM

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