12/7/7

PROJECT: Workorder Database

I'm trying to write some code for a simple database and am needing some of your VALUABLE help. I know very little about VBA and will be learning this as I go, please be patient.

I'd really like to do this in 'baby steps'. Thanks

I've formatted a spreadsheet for the data to be collected, it looks like a form.
Just some cells with data, i.e. name, address... (actually several sheets of data)

I want to take the data from the form and have a button append it to the next empty row of a different sheet.

I think I'll be using some type of automated copy/paste from there.

Please remember 'baby steps'.

If someone already has some code that would be fine, if not then...

I had the basics fully working, but have been running into problems as I refine the macro

Here's some code I wrote, it did look a whole lot better, but during the fixes, attempted fixes and additions it got disorganized...

My problem now is step 4a. copy WO# back to Woform.xls sheet Form cell j30.
I can’t get it to re-select the first wkbk, Woform.xls and paste the WO# , (you can see my attempts)
Then I want it to go back to the Wodb.xls to complete the macro.

THANKS in advance, Phil

Sub AppendWOdb()
'
' AppendWOdb Macro
' Macro recorded 12/5/2007 by Phillip O. Hasty
'


'   DONE 1. Copy job details from Woform.xls sheet CodeTables,
'   DONE 2. open WOdb,
'   DONE 3. find first blank line
'   DONE 4. add WO# and date/time,
'   4.a  copy WO# back to Woform.xls sheet Form cell j30, go back to WOdb
'   DONE 5. Paste details to that line,
'   DONE 6. Save and Close WOdb
'
'

'
'
'

    ' 1.2.3.Change sheet from 'form' to 'codetables', Copy details, Open 'WOdb.xls',
    ' find first blank line
    
    Sheets("CodeTables").Select
    Range("A1").Select                 'good starting point
    Selection.CurrentRegion.Select     'selects the first line which has the data
    Selection.Copy                     'copies the data
    'Workbooks.Open Filename:="Z:\My Documentz\WorkOrderFiles\WOdb.xls"
    Workbooks.Open Filename:="\\Clinton-dc\common\WorkOrders\WOdb.xls"  'opens WOdbxls
    Range("A1").Select                 'good starting point
    Selection.End(xlDown).Select       'selects a line down
    Selection.End(xlDown).Select       'selects a line down
    ActiveCell.Offset(1, 0).Range("A1").Select 'selects 1 cell below
    
    
    '4. add the next WO#
    ActiveCell.FormulaR1C1 = "=R[-1]+1" 'adds 1 to the number above this cell
    
    
    '4a. put wo# on form
    Selection.Copy
    Workbooks("WOform.xls").Worksheets("Form").Activate
    'Workbooks("WOform.xls").Select
    'Workbooks("WOform.xls)".Activate
    'Sheets("Form").Select
    'Range("j30").Select
    'Selection.PasteSpecial (xlPasteValues)
    'ActiveSheet.Paste Destination:=Worksheets("Form").Range("j30")

    'before proceeding, I need to go back to WOdb and the active cell


    '4. add 'date of entry' (one cell to the right of WO#)
    ActiveCell.Offset(0, 1).Select
    Dim Today
    Today = Now    ' Assign current system date and time.
    ActiveCell.Formula = Today

    
    '5. move 1 cell right and paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False

    
    
    'make Range = db
    Selection.CurrentRegion.Select
    Selection.Name = "db"
    

    
    
    'load db into form
    'Selection.Copy
    'activate Form file
    'copy to sheet FORMDB
    
    
    'go back to WOdb
    
    'save changes and close to WOdb
    ActiveWorkbook.Close SaveChanges:=True
    Range("A1").Select


    'go back to 'form' cell a1 for next WO
    Sheets("Form").Select
    Range("A1").Select


'
End Sub