Add Project to 'database'

**jieyi** · 03-09-2009, 10:42 AM

Hi,

I recorded my marco.

Sub Add_to_Project_List()
'
' Add_to_Project_List Macro
'
    Dim R1 As Range
    
'
    Range("C3").Select
    Selection.Copy
    Sheets("Project List").Select
'   Range("A2").Select
'    Range(Cells(2, 1).End(xlUp).Offset(1, 0)).Select
    ActiveSheet.Paste
    
    Sheets("Add Project").Select
    Range("C4").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("B2").Select
    ActiveSheet.Paste
    
    Sheets("Add Project").Select
    Range("C5").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("C2").Select
    ActiveSheet.Paste
    Sheets("Add Project").Select
    Range("C6").Select
 '   Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("D2").Select
    ActiveSheet.Paste
    Sheets("Add Project").Select
    Range("C14").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("E2").Select
    ActiveSheet.Paste
    Sheets("Add Project").Select
    Range("C8").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("F2").Select
    ActiveSheet.Paste
    Sheets("Add Project").Select
    Range("C9").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("G2").Select
    ActiveSheet.Paste
    Sheets("Add Project").Select
    Range("C10").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("H2").Select
    ActiveSheet.Paste
    Sheets("Add Project").Select
    Range("C11").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("I2").Select
    ActiveSheet.Paste
    Sheets("Add Project").Select
    Range("C12").Select
'    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Project List").Select
    Range("J2").Select
    ActiveSheet.Paste
End Sub

Basically my intention is to create a database filled with my projects.

1) is there a way to simplify the coding?
2) how to I add to a new line? This code always overwrite the first line.

PS: It is recorded as a marco and i have not linked it to the button.

**rylo** · 03-09-2009, 10:02 PM

HI

1) Here's some general code that will action your example. Clear out row 2 on project list, select add project, then run the code

Sub aaa()
  Dim OutSH As Worksheet, outrow As Long
  Set OutSH = Sheets("Project List")
  
  outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  For Each ce In Range("C3:C18")
    If Not IsEmpty(ce) Then
      outcol = WorksheetFunction.Match(ce.Offset(0, -2).Value, OutSH.Rows("1:1"), 0)
      OutSH.Cells(outrow, outcol).Value = ce.Value
    End If
  Next ce
  
End Sub

2) Where will the next bit of data reside? If you have a heap of data in columns A:C, then what will be your break point? Each appearance of Job Ref?

rylo

**jieyi** · 03-14-2009, 11:52 PM

Hi Rylo,

Thanks for your quote. It works fine.

what do you mean by

2) Where will the next bit of data reside? If you have a heap of data in columns A:C, then what will be your break point? Each appearance of Job Ref?

Is there a way that after adding it to the 'database' it will auto-sort it according to Column (A2 till to the end) based on number biggest till smallest. PS: Row 1 is used for me to define the parameters in each column, so that one need no be sorted.

thanks.

**rylo** · 03-15-2009, 06:09 PM

Hi

in your example file, you only have 1 item. What I was referring to was what if you have more than one data block? So if you had data going down to row 1500??? How would you separate the items?

2) Adding some sort code shouldn't be a problem. There are plenty of example on this site of sorting code. Is it a quirk of your example, but there was nothing copied over into column A???

rylo