Templates

paulsolar · Aug 21, 2013

Hi All

I'm new to forums, so I'll give it a go

I've written a workbook for one section of PAS 2030 for my friends business to help him out. I can get everything working but am now stuck. I'm not bad with excel as I've been using it for 20 years or so to run small jobs.

Basically would like to do is have a job template that has a macro to:

increment the job numbers (done this before and had it working loads of times)
clear out the name and address cells each time the number is increased (done this before and had it working loads of times)
save the completed spread sheet to a folder on the desk top (never done this before!)
the completed sheet needs to be locked for auditing purposes (never done this before!)

Anyway I managed to a Mr Excel pod cast that showed some of this but I haven't managed to get it working

could someone out there stop from going bald please as I'm pulling my hair out. i look at some of the Macros and my mind boggles!

I've been trying the macro below but for some reason it will not work and keeps coming up with an error. This macro doesn't even cover the locking of the file when it's save I know, so if someone could help with that I'd be eternally grateful

Sub nextjob()
Range("g26").Value = Range("g26").Value + 1
Range("g6:g10").ClearContents
End Sub

Sub SaveNextJobWithNewName()
Dim NewFN As Variant
'Copy Job to new workbook
ActiveSheet.Copy
NewFN = "c:\desktop\Job Folder\Job" & Range("g26").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
nextjob
End Sub

Many thanks in anticipation

Cheers

Paul

paulsolar · Aug 30, 2013

Thanks very much for you help.

I had found the problem with the Sheet 1 and changed that but I'm still getting the problem.

Like you I think I'm going to call it a night and start again tomorrow

cheers

Paul

sijpie · Aug 31, 2013

Paul, you are mixing up things. Please, slow down and try to understand and in particular look at what you are doing. First you a re working with code that we made together and nearly worked other than one small error on my part, and now you are going back to old code that you know does not work. It is very frustrating if you start changing things back, like putting the path and filename back into one string inside your macro, when i explicitly pulled it out so it can be used in the other macro as well. There is a further point for doing what i did, because then it is easier to maintain in your code: if at some point you move the files, you only need to change it in one spot. The reason why the file saving did not work initially is because you did not read my instructions to make sure the path ended with a \ and that the filename should be without the path. That is why i say, slow down! Read what i and others are telling you.

and please, start debugging as explained in my guide to better VBA, step through the code, then at least you will understand far better what is happening. Do yourself this favour.

when i get a chance to get behind a pc i will make the correction in the code and post it.

paulsolar · Aug 31, 2013

Hi Sijpie

Many thanks for all your help

I've kept a copy of your original code safe. I'm learning as I go along here and finding it a difficult learning curve. The debugging is teaching me a lot as I progress. I've been using some of the old code to see what makes things tick, as a learner I think this is important.

When I originally started I didn't know anything about code or what it did, now I'm getting to understand it and how it moves data around. I think without yours and everyone on this forums help I'd be stumbling in the dark completely, you have all given me a candle to work with. When I've finished this project I am determined to carry on with other projects as the more I learn the more I am enjoying it. I'm sure that over the next few months the light will come on a little brighter as I get to understand it more

I'll leave it with you until later and wont do any more for now.

Just to get you up to date, I found one problem which was that I had Sheet 1 instead of Sheet1 as the tab.

The main workbook that the macro is attached to is:
C:\Users\Paul Breen\Desktop\Dry House\Dry House Job Management & Records\Job Entry Workbook.xlsm and the info is on Sheet1

The Folder that the whole job record needs to be saved into is:
C:\Users\Paul Breen\Desktop\Dry House\Dry House Job Management & Records\Job Numbers

The workbook that the cell details need to be copied to for quick reference is:
C:\Users\Paul Breen\Desktop\Dry House\Dry House Job Management & Records\Job Numbers\Job register.xlsx and the info goes to Sheet1

Once again thanks to everyone for reading my posts and helping.

Kind regards

Paul

ps I wish I had started learning this at sixteen not sixty my mind may have taken it in better then!

sijpie · Sep 3, 2013

Hey there is still hope! I'm only just behind youin age (although i have been prgramming for a long time). And there is a lot of online training material.

Also I can recommend a book which is a bit higher level stuff, but excellent explanations, and one of the authors happens to be Bill Jelen, or Mr Excel (ie this website's owner...)
[h=1]VBA and Macros: Microsoft Excel 2010 VBA and Macros: Microsoft Excel 2010 (MrExcel Library): Bill Jelen, Tracy Syrstad: 9780789743145: Amazon.com: Books[/h][h=1]which you also cen get digitally

Here goes the next version of the code I have put your paths in the right places and changed the name of the register file.[/h]
Option Explicit ' following constants hold the PATH to files to be used _ do NOT include file names. End with \ Const sJobFilePATH = "C:\Users\Paul Breen\Desktop\Dry House\Dry House Job Management & Records\Job Numbers\" Const sRegFilePATH = "C:\Users\Paul Breen\Desktop\Dry House\Dry House Job Management & Records\Job Numbers\" '---------------------------------------------- Sub TransfertoRegister() ' ' Sub to transfer job details to register file '---------------------------------------------- Dim sJobnumber As String, sJobdate As String, sName As String, _ sAddressline1 As String, sAddressline2 As String, _ sAddressline3 As String, sPostcode As String, _ sNameofEngineer As String Dim wbMyData As Workbook Dim wsOut As Worksheet Dim bCloseFlag As Boolean Dim lRowCount As Long ' in following constant File name only, no path! Const sRegFileNAME = "Job Register.xlsx" With Worksheets("Entry Sheet") sJobnumber = .Range("g26") sJobdate = .Range("g18") sName = .Range("G6") sAddressline1 = .Range("G7") sAddressline2 = .Range("G8") sAddressline3 = .Range("G9") sPostcode = .Range("G10") sNameofEngineer = .Range("g20") End With ' Check if Registry file is open On Error Resume Next Set wbMyData = Workbooks(sRegFileNAME) On Error GoTo 0 If wbMyData Is Nothing Then ' file is not open yet ' open file and set flag to remember to close again Set wbMyData = Workbooks.Open(sRegFilePATH & sRegFileNAME) bCloseFlag = True End If Set wsOut = wbMyData.Sheets("sheet1") ' transfer details to Job Registry file, add to end With wsOut.Range("A1") lRowCount = .CurrentRegion.Rows.Count + 1 .Offset(lRowCount, 0) = sJobnumber .Offset(lRowCount, 1) = sJobdate .Offset(lRowCount, 2) = sName .Offset(lRowCount, 3) = sAddressline1 .Offset(lRowCount, 4) = sAddressline2 .Offset(lRowCount, 5) = sAddressline3 .Offset(lRowCount, 6) = sPostcode .Offset(lRowCount, 7) = sNameofEngineer End With ' add hyperlink to the jobfile for easy access With wsOut .Hyperlinks.Add anchor:=.Range("A1").Offset(lRowCount, 0), _ Address:=sJobFilePATH & "Job" & _ .Range("A1").Offset(lRowCount, 0).Value & ".xlsx" End With ' save registry and close if it wasn't already open If bCloseFlag Then wbMyData.Close savechanges:=True Else wbMyData.Save End If 'clean up Set wbMyData = Nothing Set wsOut = Nothing End Sub '---------------------------------------------- Sub NextJob() ' ' Macro to clear form, ready for next entry '---------------------------------------------- Range("g26").Value = Range("g26").Value + 1 Range("g6:g11").ClearContents Range("g19:g20").ClearContents Range("g24").ClearContents Range("g31:g43").ClearContents ' Cell g13:g17 has validation list Range("G13:G17").ClearContents End Sub '---------------------------------------------- Sub SaveNextJobWithNewName() ' ' macro to save entry form to new file for _ archiving. Sheet is password protected _ with random password to protect against _ changes '---------------------------------------------- Dim sPW As String Dim iArr(1 To 10) As Integer, i As Integer Dim NewFN As String ' set file name for job NewFN = sJobFilePATH & Range("g26").Value & ".xlsx" ' fill password string with 10 random chars > 20 (exclude problem chars) For i = 1 To 10 sPW = sPW & Chr(CLng(Rnd(Time) * 200 + 20)) Next i 'copy job sheet to new workbook ActiveSheet.Copy With ActiveSheet With .UsedRange ' transform formulas to values .Value = .Value ' set cells to locked .Locked = True End With ' protect with random password .Protect sPW End With ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close 'transfer data to register TransfertoRegister MsgBox "Job" & Range("g26").Value & ".xlsx registered & saved" 'clear form for next job NextJob End Sub

Templates

paulsolar

Well-known Member

paulsolar

Well-known Member

Excel Facts

sijpie

Well-known Member

paulsolar

Well-known Member

sijpie

Well-known Member

Similar threads

Forum statistics

Share this page

Templates

paulsolar

Well-known Member

paulsolar

Well-known Member

Excel Facts

sijpie

Well-known Member

paulsolar

Well-known Member

sijpie

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock