How do i write code for saving a pdf based on a cell name and also how to auto copy ?

I have 3 worksheets; REPORT, RECORD, and SHADOW RECORD. Report is filled out by the user and when they fill it in the data appears in SHADOW RECORD. When they click SAVE the data in SHADOW RECORD is copied from shadow record to RECORD. After which, the report that the user fills in is then saved as a pdf with the default name of the Inspection Number.

However I am having problems writing code for this as I have done this manually and I think it is one of those things where the code is much more efficient than doing it manually. This is what my manual steps look like :

Please Login or Register  to view this content.

Basically when I run the macro the information being copied from SHADOW RECORDS to RECORDS is not consistent and when I did it manually I inserted a row manually after I had pasted special (values) into the first row underneath the column titles in row 1. The pdf can be created but the saving of it is difficult as you are having to manually save the document rather than doing it automatically. Should I ditch the manual and go vba ? and what should my code look like?

Hi, Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi,

Thanks for editing the code tags.

Can you just confirm the basic tasks here.
One task seems to be to copy the shadow record to the record sheet, and the other is to save the pdf file. If so then the following should be sufficient

Please Login or Register  to view this content.

Note that it's rarely necessary to .Select and .Activate stuff. Jumping backwards and forewards between VBA and the Excel App to do this carries a time overhead and it's just inefficient.

Hi,

You should use the sheet VBA code names that I showed in my example instead of the 'Record, 'ShadowRecord'...etc.
i.e. Sheet2, Sheet3 etc..

It's good practice to always use the VBA code names since it's all to easy to change the sheet tab names and once you do that the macro will fall over. The VBA code name is the first bit of the sheet description, the tab name is the bit in parentheses. So where you see
Sheet1(Report) in the VBE the VB code name is Sheet1 and the tab name is Report.

You should also move the vertical page break on the Report tab otherwise the pdf file will spread over two pages.