Recording the contents of a cell in a macro

[darkwing_duck]

I have a spreadsheet that I'm using to create quotes. I've created a
formula to create a random number (ok, I know it's not really random
and unrepeating but for my purposes, it's close enough) to represent
the quote number. I've recorded a macro that copies the entire
workbook and paste's special values to lock in the quote number. Then
I go to the cell that contains the quote number and copy the contents
of the cell so that when I print to PDF, I want to paste that quote
number in as the filename. However, it never seems to either copy the
contents or paste them (don't know which). I've tried highliting the
contents of the cell up top and copying and I've tried to highlite the
cell and copying that. In the latter case, when it pastes in the
filename, it pastes a blank entry (as in it overwrites the default
name (the name of the file) as no text.

I'm using Excel 2007 in XP.

TIA,
Robert

 

[CurlyDave]

I have a spreadsheet that I'm using to create quotes.  I've created a
formula to create a random number (ok, I know it's not really random
and unrepeating but for my purposes, it's close enough) to represent
the quote number.  I've recorded a macro that copies the entire
workbook and paste's special values to lock in the quote number.  Then
I go to the cell that contains the quote number and copy the contents
of the cell so that when I print to PDF, I want to paste that quote
number in as the filename.  However, it never seems to either copy the
contents or paste them (don't know which).  I've tried highliting the
contents of the cell up top and copying and I've tried to highlite the
cell and copying that.  In the latter case, when it pastes in the
filename, it pastes a blank entry (as in it overwrites the default
name (the name of the file) as no text.

I'm using Excel 2007 in XP.

TIA,
Robert

Just curious if you tried the key strokes?
Ctrl&c to copy
Ctrl&v to paste

as in some cases the mouse control does not paste..
for example, if you wanted to paste text into xl's find command, the
mouse will not paste but Ctrl&v will...

 

[rbrown999]

Just curious if you tried the key strokes?
Ctrl&c to copy
Ctrl&v to paste

as in some cases the mouse control does not paste..
for example, if you wanted to paste text into xl's find command, the
mouse will not paste but Ctrl&v will...- Hide quoted text -

- Show quoted text -

I'm sorry, I didn't get notified of a reply.

Yes, I did try both the keyboard & mouse shortcuts. I've dug a bit
deeper and the issue isn't that it won't copy - so let me try and
explain it differently:

I'm trying to create a sales quoting tool in Excel. Quotes should
have a unique quote number. If you change an expiration date, you
should have a new quote number. If you change a quantity, you should
have a new quote number. So ... I created a quote number that is
randomly (or as best as Excel can do) generated each time there's a
calculation done on a quote. What I want to do is print that quote to
a PDF for distribution, so I make a copy of the worksheet so I don't
screw up my template, then copy the entire worksheet and "paste
special, values". Then I want to save the PDF as the quote number, so
I highlight the cell containing the quote number, copy the contents of
that cell, and paste into the filename when the print to PDF dialog
opens up. HOWEVER: the macro records me copying the "specific quote
number" for that "specific example". The next time I do a quote, the
quote number will be different, so when the macro tries to copy that
previous sequence of characters and numbers, they don't exist, so it
doesn't copy anything. Because it doesn't copy anything, it can't
paste anything. I've tried highlighting the text within the cell and
highlighting the cell itself, neither works.

So my bottom line question is: How do I tell a macro to copy the
contents of a cell that will be different each time, so that it can
paste it in the next dialog box?

 

[Pete_UK]

You can't do it when you record the macro - you need to go in
afterwards and edit the macro. Post a copy of your macro here, and I'm
sure someone will respond.

Pete

 

[rbrown999]

You can't do it when you record the macro - you need to go in
afterwards and edit the macro. Post a copy of your macro here, and I'm
sure someone will respond.

Pete






- Show quoted text -

Thanks for the response. Here's my macro:

Sub PrintQuote()
'
' PrintQuote Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Sheets("QuoteTool").Select
Sheets("QuoteTool").Copy After:=Sheets(2)
Sheets("QuoteTool (2)").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("H8:I8").Select
ActiveCell.FormulaR1C1 = "QUO-DWGLG-59891"
Range("D8").Select
Application.ActivePrinter = "Adobe PDF on Ne04:"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on
Ne04:"",,TRUE,,FALSE)"
Sheets("QuoteTool (2)").Select
ActiveWindow.SelectedSheets.Delete
Sheets("QuoteTool").Select
End Sub