Copy & paste from filtered row

[johnpants]

Hi, I have a workbook that has 2 sheets, 'Data' and 'Invoice'. On the data sheet the user simply adds in details about orders, and the invoice sheet is an invoice layout to print.

What I would like to do is for them to filter the data to find the record they want, then click a button and it enters this data into the relevent cells on the invoice sheet.

I have done a similar thing like this before using (xlCelltypeVisible) but that was to copy a whole row and i'm having trouble getting it to work for individual cells.

Can anyone help with this please?

Thanks,

john.

 

[Swaroon]

Hi John,

You could try using "Data / Filter / Advanced Filter" but would need to set up the invoice template on the same sheet as the data.

List Range = Data
Criteria Range = records they want
Copy To = Your template invoice

You could macro it up to make it more user friendly but it's maybe prone to possible user error - there's undoubedly a better way to get there !

 

[johnpants]

Thanks, I have tried the advanced filter option but having the invoice on the same sheet just isn't practical enough.

I am loking at the macro way, and have searched through the forums but am still unable to find the correct code to make it only copy the 1 cell of the filtered data.

 

[johnpants]

I have tried recording a macro to get the code but it refuses to copy the cell in column A that is filtered. It only copies the Cell A4...

Sub invoice()
Selection.SpecialCells(xlCellTypeVisible).Select
    Range("A4").Select
    Selection.copy
    Sheets("Invoice").Select
    Range("A12:E12").Select
    ActiveSheet.Paste
End Sub

 

[Dsuperc]

Here ya go:

Sub DelFiltRow()

Dim LastRow, r As Integer

LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1

Application.ScreenUpdating = False

For r = LastRow To 2 Step -1
If Not Rows(r).Hidden Then Rows(r).Delete
Next r

End Sub

 

[johnpants]

Thank you for this, but I am unsure exactly what this is trying to do, and it seems to fall over.