Conditional selecting and pasting in excel

Question

Hi guys,

I am quite new to VBA coding so I was hoping you could help me with the following problem.

I am looking for the best way to organize the following:

From one set of data I am getting different sorts of documents (all have a certain document type) with their information (e.g. customer name, address, amount, VAT,...). From this file I want to select certain doc types (e.g. DG, EG, SA, ...) which defer every time and copy paste those rows pertaining to those items.

e.g. of the data I am getting

Customer Name Date Amount Tax Discount Doc Type

25739484 Bert 01/01/2010 100 15% 2% EG

Now my question is:

1. What is the easiest way to say for which doc types I want to have the data selected and pasted. (this file is for reuse accross the company). Let the users put them in different cells?
2. Based on the doc types the users then select, how can I make the macro select those rows and copy them to a new file?

Thank you so much!!!

Ellen

Answer 1

Please note this is not complete and I did not fully test it. I hope this helps get you started.

Dim dt As String
Dim ws As Worksheet
Dim cnt As Long
Dim done As Boolean
Dim emptycount As Long

'ask the user for the doc type
dt = InputBox("Enter the doc type")

'get the active sheet
ws = ThisWorkbook.ActiveSheet

If dt <> "" Then
    'loop over rows
    Do While Not done
        cnt = cnt + 1
        'compare the doc type column to the doc type they selected
        If ws.Cells(cnt, 6) = dt Then
            'copy the row here
        End If

        'keep track of "empty" rows, after 1000 emptys, exit the loop
        If ws.Cells(cnt, 6) = "" Then emptycount = emptycount + 1
        If emptycount = 1000 Then done = True
    Loop
End If