Filtering information

[Decreenisi]

I have a workbook(Excel 2007), with 2 sheets the first is called data
log the second is called input data. The actual data is a list of
records for our concern management system, as below

A B
C D E
F G
1 DATE Kanban Part No Part
name Colour Fault Action
2
3 23-Aug-09 C654 12548-25-558 Assy wper 5
Blue Ext blister Rejected

In Input data I have arranged the cell and formatted them to look like
an Access form. Column C, D, E are VLookup from the data in column B.
Column F & G have Validation list boxes in each of the cells.

Anyway, when I have entered the data in the cell in Input data sheet,
I would like a macro attached to a button to goto the data log sheet,
find the next free row and input data from Input data to specific
cells in data log sheet. Some of this data may be lookup or list
boxes.

I have tried and tried to do this and have got no where. The data
doesn't copy & past well, perhaps I should have tried paste special/
values ?

Can anyone help please.

Best regards

Duncan.

 

[OssieMac]

Hi Duncan,

Not sure that I have interpreted your question correctly because it appears
to be just duplicating the data. However, the following copies data from a
single row at the active cell in the active sheet and Pastes Special to the
Data Log sheet.

I have assumed that you have column headers in the Data Log sheet and they
are similar to the Input Data sheet.

Note the comments. Note also that a space and underscore at the end of a
line is a line break in an otherwise single line of code.

Ensure that you backup your workbook before running the code in case it does
not do what you expect.

Sub CopyData()
Dim wsDataLog As Worksheet

Set wsDataLog = Sheets("Data Log")

'On Input Data sheet at the activecell row, _
copy the range from column A to column G
ActiveSheet.Range(Cells(ActiveCell.Row, "A"), _
Cells(ActiveCell.Row, "G")).Copy

'Find the blank row at the bottom of _
existing data in Data Log and PasteSpecial, values
With wsDataLog
.Cells(.Rows.Count, "A").End(xlUp) _
.Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues
End With

End Sub

 

[Decreenisi]

Hi Duncan,

Not sure that I have interpreted your question correctly because it appears
to be just duplicating the data. However, the following copies data from a
single row at the active cell in the active sheet and Pastes Special to the
Data Log sheet.

I have assumed that you have column headers in the Data Log sheet and they
are similar to the Input Data sheet.

Note the comments. Note also that a space and underscore at the end of a
line is a line break in an otherwise single line of code.

Ensure that you backup your workbook before running the code in case it does
not do what you expect.

Sub CopyData()
Dim wsDataLog As Worksheet

Set wsDataLog = Sheets("Data Log")

'On Input Data sheet at the activecell row, _
 copy the range from column A to column G
ActiveSheet.Range(Cells(ActiveCell.Row, "A"), _
        Cells(ActiveCell.Row, "G")).Copy

'Find the blank row at the bottom of _
 existing data in Data Log and PasteSpecial, values
With wsDataLog
    .Cells(.Rows.Count, "A").End(xlUp) _
        .Offset(1, 0).PasteSpecial _
        Paste:=xlPasteValues
End With

End Sub

Thanks, that's just what I needed.

Dun.