nomaxtech
01-13-2011, 06:54 AM
Hi everyone,
I've been working on this for days and must have visited a hundred websites with no luck. I've seen examples that might help but they weren't explained enough to understand. Usually the code was accompanied by "Just paste this in there."
Here's my deal:
I want to have a master price list in a seperate file editable by only me. I'm going to ditrubte Purchase orders, Service Records, and Job Costing sheets around the office so people can just click a button on the sheet and retrieve the most up-to-date product list and pricing from my master file. I have that solved so far.
Now, some of these people my want only alarm equipment, some of them may want only video equipment, and some of them may want both of those and some others. In my master sheet I have a column solely dedicated to the category of the equipment for this purpose. If they check Alarm and click the button, then only get the alarm list. If they check CCTV, they only get the video stuff. Etc...
I'll use a job costing sheet, for example. I have a Sub that runs as many times as there are checked checkboxes. Because it's the same code that run over and over again (with just the category changed), I tried to put it into it's own Sub and then call it as many times as I need it purely for simplifying the code.
Here's my example: (it's all indented and neatly organized in Excel)
Sub Retrieve_Equipment()
'**********
'Retrieve Equipment List from Master Cost Sheet
'**********
'**********
'Determine if specified box is Checked, continue routine if it is
'**********
If (Range("Q6") = "True") Then
Copy_and_Paste
End If
End Sub
'----------------------------------------------------------------
Sub Copy_and_Paste()
Set JC = Sheets("Job Costing")
Set TC = Sheets("True Cost")
FR = 2
LR = LastRow()
'**********
'Starting at row 2, perform routine until cells in column A are empty
'**********
Do Until IsEmpty(TC.Range("A" & FR))
'**********
'Perform routine for any rows whose value is equal to Cat in colum A of specified sheet
'**********
If TC.Range(("A" & FR) = "Alarm") Then
'**********
'Start operation row after the last row with data (first empty row)
'**********
LR = LR + 1
'**********
'Call cell format routine
'**********
Merge_and_Format
'**********
'Copy rows and specified columns to first empty row on destination sheet
' from current row on source sheet then add formula "=A(current row)*J(current row)"
'**********
JC.Rows(LR).Columns("C").Value = TC.Rows(FR).Columns("C").Value
JC.Rows(LR).Columns("E").Value = TC.Rows(FR).Columns("D").Value
JC.Rows(LR).Columns("J").Value = TC.Rows(FR).Columns("E").Value
JC.Rows(LR).Columns("L").Formula = "=A" & LR & "*J" & LR
End If
'**********
'Go to next row on source sheet
'**********
FR = FR + 1
Loop
End Sub
So....if you can follow it, the Copy_and_Paste Sub is looking for everything in the Alarm category right now. I'd have to write the same Sub over and over looking at different categories this way. I'd like to be able to change what it's looking for whenever it's called from inside the Retrieve_Equip Sub. I can't figure out those darn arguments though...(or even if that's how to get this done) Here's what I was to be able to do:
Sub Retrieve_Equipment()
'**********
'Retrieve Equipment List from Master Cost Sheet
'**********
'**********
'Determine if specified box is Checked, continue routine if it is
'**********
If (Range("Q6") = "True") Then
Copy_and_Paste ("Alarm")
End If
If (Range("Q7") = "True") Then
Copy_and_Paste ("CCTV")
End If
If (Range("Q8") = "True") Then
Copy_and_Paste ("Wire")
End If
End Sub
'----------------------------------------------------------------
Sub Copy_and_Paste(**VARIABLE**)
Set JC = Sheets("Job Costing")
Set TC = Sheets("True Cost")
FR = 2
LR = LastRow()
'**********
'Starting at row 2, perform routine until cells in column A are empty
'**********
Do Until IsEmpty(TC.Range("A" & FR))
'**********
'Perform routine for any rows whose value is equal to Cat in colum A of specified sheet
'**********
If TC.Range(("A" & FR) = "**VARIABLE**") Then
'**********
'Start operation row after the last row with data (first empty row)
'**********
LR = LR + 1
'**********
'Call cell format routine
'**********
Merge_and_Format
'**********
'Copy rows and specified columns to first empty row on destination sheet
' from current row on source sheet then add formula "=A(current row)*J(current row)"
'**********
JC.Rows(LR).Columns("C").Value = TC.Rows(FR).Columns("C").Value
JC.Rows(LR).Columns("E").Value = TC.Rows(FR).Columns("D").Value
JC.Rows(LR).Columns("J").Value = TC.Rows(FR).Columns("E").Value
JC.Rows(LR).Columns("L").Formula = "=A" & LR & "*J" & LR
End If
'**********
'Go to next row on source sheet
'**********
FR = FR + 1
Loop
End Sub
Any ideas? Thanks in advance!
I've been working on this for days and must have visited a hundred websites with no luck. I've seen examples that might help but they weren't explained enough to understand. Usually the code was accompanied by "Just paste this in there."
Here's my deal:
I want to have a master price list in a seperate file editable by only me. I'm going to ditrubte Purchase orders, Service Records, and Job Costing sheets around the office so people can just click a button on the sheet and retrieve the most up-to-date product list and pricing from my master file. I have that solved so far.
Now, some of these people my want only alarm equipment, some of them may want only video equipment, and some of them may want both of those and some others. In my master sheet I have a column solely dedicated to the category of the equipment for this purpose. If they check Alarm and click the button, then only get the alarm list. If they check CCTV, they only get the video stuff. Etc...
I'll use a job costing sheet, for example. I have a Sub that runs as many times as there are checked checkboxes. Because it's the same code that run over and over again (with just the category changed), I tried to put it into it's own Sub and then call it as many times as I need it purely for simplifying the code.
Here's my example: (it's all indented and neatly organized in Excel)
Sub Retrieve_Equipment()
'**********
'Retrieve Equipment List from Master Cost Sheet
'**********
'**********
'Determine if specified box is Checked, continue routine if it is
'**********
If (Range("Q6") = "True") Then
Copy_and_Paste
End If
End Sub
'----------------------------------------------------------------
Sub Copy_and_Paste()
Set JC = Sheets("Job Costing")
Set TC = Sheets("True Cost")
FR = 2
LR = LastRow()
'**********
'Starting at row 2, perform routine until cells in column A are empty
'**********
Do Until IsEmpty(TC.Range("A" & FR))
'**********
'Perform routine for any rows whose value is equal to Cat in colum A of specified sheet
'**********
If TC.Range(("A" & FR) = "Alarm") Then
'**********
'Start operation row after the last row with data (first empty row)
'**********
LR = LR + 1
'**********
'Call cell format routine
'**********
Merge_and_Format
'**********
'Copy rows and specified columns to first empty row on destination sheet
' from current row on source sheet then add formula "=A(current row)*J(current row)"
'**********
JC.Rows(LR).Columns("C").Value = TC.Rows(FR).Columns("C").Value
JC.Rows(LR).Columns("E").Value = TC.Rows(FR).Columns("D").Value
JC.Rows(LR).Columns("J").Value = TC.Rows(FR).Columns("E").Value
JC.Rows(LR).Columns("L").Formula = "=A" & LR & "*J" & LR
End If
'**********
'Go to next row on source sheet
'**********
FR = FR + 1
Loop
End Sub
So....if you can follow it, the Copy_and_Paste Sub is looking for everything in the Alarm category right now. I'd have to write the same Sub over and over looking at different categories this way. I'd like to be able to change what it's looking for whenever it's called from inside the Retrieve_Equip Sub. I can't figure out those darn arguments though...(or even if that's how to get this done) Here's what I was to be able to do:
Sub Retrieve_Equipment()
'**********
'Retrieve Equipment List from Master Cost Sheet
'**********
'**********
'Determine if specified box is Checked, continue routine if it is
'**********
If (Range("Q6") = "True") Then
Copy_and_Paste ("Alarm")
End If
If (Range("Q7") = "True") Then
Copy_and_Paste ("CCTV")
End If
If (Range("Q8") = "True") Then
Copy_and_Paste ("Wire")
End If
End Sub
'----------------------------------------------------------------
Sub Copy_and_Paste(**VARIABLE**)
Set JC = Sheets("Job Costing")
Set TC = Sheets("True Cost")
FR = 2
LR = LastRow()
'**********
'Starting at row 2, perform routine until cells in column A are empty
'**********
Do Until IsEmpty(TC.Range("A" & FR))
'**********
'Perform routine for any rows whose value is equal to Cat in colum A of specified sheet
'**********
If TC.Range(("A" & FR) = "**VARIABLE**") Then
'**********
'Start operation row after the last row with data (first empty row)
'**********
LR = LR + 1
'**********
'Call cell format routine
'**********
Merge_and_Format
'**********
'Copy rows and specified columns to first empty row on destination sheet
' from current row on source sheet then add formula "=A(current row)*J(current row)"
'**********
JC.Rows(LR).Columns("C").Value = TC.Rows(FR).Columns("C").Value
JC.Rows(LR).Columns("E").Value = TC.Rows(FR).Columns("D").Value
JC.Rows(LR).Columns("J").Value = TC.Rows(FR).Columns("E").Value
JC.Rows(LR).Columns("L").Formula = "=A" & LR & "*J" & LR
End If
'**********
'Go to next row on source sheet
'**********
FR = FR + 1
Loop
End Sub
Any ideas? Thanks in advance!