Want to move several cells info to next available row on master sh

M

Muddled

I have a sheet called "quotes." I have another in the same workbook called
"Jobs".
When a "Quote becomes a "Job", I want the "quote # cell" & the "job name" on
the same row a couple of cells over, to appear in the next available row on
the "Job" sheet.
Any suggestions?

DC
-
 
J

JMay

Watch for possible column change in Standard module below **

Afterwards Just double-Click on the Completed Quote Number on your Quotes
sheet
and the two values will be copied to your Jobs Sheet.

In your Quotes Object Code sheet paste in:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
Call foo
End Sub

In a Standard Module paste in:

Sub foo()
Dim Qcell As String
Dim Jcell As String
Qcell = ActiveCell.Address(0, 0)
Jcell = ActiveCell.Offset(0, 2).Address(0, 0) ' **assumes JobName in
Column C**
Sheets("Jobs").Activate
nRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Quotes").Range(Qcell).Copy Sheets("Jobs").Range("A" & nRow)
Sheets("Quotes").Range(Jcell).Copy Sheets("Jobs").Range("B" & nRow)
End Sub
 
M

Muddled

A couple questions before I attempt this. I had made a column on my Quotes
sheet to put a check mark in or a value above 0, which allowed the info to
transfer to the job sheet. If I understand your suggestion, I will simply
double click on a quote that has become a job. Will I have any visual
notification on the quote sheet that it has become a job? If I double click
it again, will it again take a new slot on the jobs page? This could cause me
grief.
Next I don't understand some of the jargon:
1. What is the" standard module below" refering to?
2. Column change?
3.What or where is my "Quotes Object Code"?

Your help is much appreciated.
DG

-
 
J

JMay

You could insert a line of code (next to last line - before the End Sub) like
so..

Msgbox "This Specific Quote data has been copied to the Jobs Sheet"

Click on the cell of the Quote Number you wish to copy before
double-clicking it - Cause it will transfer the info on the Quote line
CURRENTLY SELECTED.

And yes, you can do it twice, or three times -- If you should duplicate,
just delete it
from the Jobs Sheet where/when duplicated.

1. What is the" standard module below" refering to?
It is a module you create in the VBE by clicking on Insert, Module (if none
already exists) - but if you already have one (Module1 default named) then
just paste the code into it.
2. Column change?
Click to expand...
I had to ASSUME your JobName was in Column C of your Quotes Sheet -- If it
is not(say it is in Column F), then you will have to change to the code
column reference # from 2 to 5).
3.What or where is my "Quotes Object Code"?
Click to expand...
In the VBE Project Explorer Window (Top Left - what looks like the old File
Manager - Tree style framework) click on your Sheet?(Quotes) - then at the
menu, click View, Code. Your circur should jump to the spot where you should
paste in the given code supplied.
 
M

Muddled

JMay,
Looking for more help. I Have been looking up what several things are which
I had never used before. VBE,VBA, Now have at least some meaning for me. Also
I see A device called Macro. I Managed to paste your formulas onto a macro
Module, However I suspect I am coming at it wrong somehow. The three lines
below I found on google. I Have been unable to get into the macro or VBE file
in the Manner described there. -- I Can't locate a "Tools" selection in the
main menu of excel. Neither can I understand or find a file called Microsoft
excel excercises.( Maybe I am to make a file with this name?) & Of course
with no "tools", I couldn't find VBE Editor. I was however able to change a
file name to "Pizza Store". & yes I have my "JobName" In column C, Thank you.
DC
--

Locate your Microsoft Excel exercises folder and display it in the Save In
combo box

Change the File Name to Pizza Store and click Save
 
Top