automatically move data to another worksheet

[welsh_decs]

I am not sure which section this would come under.

I work for a company that has 8 or 9 holiday parks.

We have a spreasheet for the company credit card.

The first sheet has all the credit card transactions, one of the columns on this sheet is named "Park" where we input the name of the park that the transaction is for.

then there are 8 other sheets one for each of the parks.

Is there a formula or anything I can do that when we input the name of the park into the "Park" column that it automaticall transfers that row of information into the correct sheet for that park.

I did try to do one with a macro and it worked however, I had to click the update entry button (i created to run the macro) everytime I entered a line, and also if i had 2 entries for one park the second one would overwrite the first one (it wouldnt go to the next available row)

 

[welsh_decs]

after all i have had to greatly move it around alot.

I have had to change column names and insert quite a few more.

I have managed to sort out where the date entry box appears however,

each row needs to be transfered to the master tab and also the tab with the name thats matches what is in the "merchant ID"

when ever I press the "transfer rows" button (positioned at the end of row 1) it doesn't work (it seems like it gets as far as cell b2 and no further)

this spreadsheet is for transactions we receive by credit card and sometimes they do not have a startdate and so thats why 00/00 is displayed in cell B2

If it needs to sort by date then the column marked "trans. date" is the one that is why i have got the date entry box appearing there

I have attached the spreadsheet any help would be greatly appreciated.

https://www.dropbox.com/s/nmncckykqqearlk/Declan Spread.xlsm

 

[welsh_decs]

any ideas guys?

 

[apo]

Hi..

Sorry for the late reply.. the notification system hasn't been working properly on the Board.. it seems to be working now.. ( I just got flooded with about 20 emails (replies to threads i have been involved in and PMs's etc)..

I will have a look at this tonight or tomorrow..

At a quick look.. you having issues because you have changed the layout or the sheets and need to adjust the code accordingly..

Also.. i noticed your last sheet(Vale Holiday Parks) is a completely different layout to the rest of the sheets.. any reason for that.. ? What data is meant to be transferred to this sheet..?

Also.. you don't have validation lists for the Park names in your adjusted "Entry" sheet.. did you still want them ?

My advice would be to have Val Lists (dropdowns) for a fair few of your fields on that Entry sheet like Transaction Status, Card Type, Acq./Proc. , Settlement Currency etc..

To do that.. I will need to know all the possible options for each f those columns..

I would also whack the Datepicker on the Card Expiry column (formatted to be mm/yyyy)..

 

[welsh_decs]

Basically in the last sheet "vale holiday parks" that is an over sight on my part it's supposed to be exactly the sane layout as the rest.

I was hoping to avoid as many drop down lists as possible.

i only figured how to change where the date selector appeared I don't think I would be able to add more cells where it appears.

also with the "card start" (column b) most cards won't have a date (as a lot of debit cards don't have a start date) so this is why 00/00 is entered so I don't think a date selector would work there.

sorry if I sounded impatient I didn't realise it was just you that helped out I thought it was an open forum where everyone advised everyone my misunderstanding

 

[apo]

I was hoping to avoid as many drop down lists as possible.

Why.. drop downs mean you don't have to type anything and if you ever have to validate using the value in that cell.. it will always be spelt/formatted correctly..

sorry if I sounded impatient I didn't realise it was just you that helped out I thought it was an open forum where everyone advised everyone my misunderstanding

Yeah.. it is an open forum.. anyone can post in a thread... sometimes a thread will have multiple offering different solutions.. sometimes none..

I iwll post back when I have made some changes to your workbook..

 

[welsh_decs]

thank you very much for your help it is truly appreciated

 

[Jerk24]

Good morning. If you don't mind i would like to Piggy back of this post as i have would like to do the same sort of thing.

I pull a report that will contain quite a few different task. I have copied the BEGINING OF THE TASK TITLE (some task are too long so they are abreviated). the task name is in (D:D the list size varies from week to week)

how can i make everything move to the sheet name if column D matches the sheet?

Thank you in advance.

 

[welsh_decs]

sorry if it seems impatient I was just wondering if there was an update on this

 

[apo]

Hi.. Here is the changed code and Workbook with code added..

Private Sub CommandButton1_Click()
    Dim lr As Long, lr2 As Long, n As Long
    Dim CpyRng As Range, CpyToRng As Range, rRngToClear As Range, CpyToMaster As Range

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    With Sheets("Entry").Range("A1:AB350")
        For i = 3 To Worksheets.Count
            lr = Sheets(Sheets(i).Name).Range("A" & Rows.Count).End(xlUp).Row + 1
            lr2 = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row + 1
            .AutoFilter 7, Sheets(i).Name
            n = Range("A1:AB350").SpecialCells(12).SpecialCells(2).Count

            If n > 28 Then
                'Setup the Ranges to Copy From and To
                Set CpyRng = Sheets("Entry").Range("A1").CurrentRegion.Offset(1).SpecialCells(12)
                Set CpyToRng = Sheets(Sheets(i).Name).Range("A" & lr)
                Set CpyToMaster = Sheets("Master").Range("A" & lr2)
                CpyRng.Copy Destination:=CpyToRng
                CpyRng.Copy Destination:=CpyToMaster

                'Build Range that will be cleared
                If rRngToClear Is Nothing Then
                    Set rRngToClear = CpyRng
                Else
                    Set rRngToClear = Union(rRngToClear, CpyRng)
                End If
            End If
        Next i
        ' Clear the rows that were transferred
        If Not rRngToClear Is Nothing Then
            rRngToClear.ClearContents
        End If

        .AutoFilter
    End With
    Cells(2, 2).Select
    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Declan Spread

@Jerk24..

If you don't mind i would like to Piggy back of this post as i have would like to do the same sort of thing

As polite as you were.. what you're wanting to do is known as "hijacking a thread"..

It's not fair to try to take away the focus on the OP's particular issue..

You can however post a new thread and link back to this Thread as a reference..

 

[welsh_decs]

thank you so much for all your help