VBA Code to Move row if criteria to tab within same Workbook

[Geeta2013]

Hi All - I need some help. I am having trouble. thought this would be easier.

Issue: This 'Master tab is updated regularly. I am looking to do this: If Column B in the Master Tab is updated with '55-04' on say Row 7, then I want a code that will move that data to the first empty row in the Tab with with name 55-04. I would need it to account for new Account numbers being added on the Master tab, and then create a new tab and have the code update that new tab also.

Just to clarify, I need to move Columns A:G on Master, to Columns A:G on the relevant tabs.

Hope I am clear. See attached file.


Regards, Geeta

[vcoolio]

Hello Geeta,

Looks like the fish are not biting!

Do you still need help on this one?

Cheerio,
vcoolio.

[Geeta2013]

Yes I do vcoolio..it is either too easy for the pros and too difficult for me....and I don't have half a code to give to you either : (. thank you. Geeta

[vcoolio]

Hello Geeta,

Well, I'm certainly not a pro, more of a "battler" I would say but I have the following for you.

The following code is a Worksheet_Change event and needs to be placed in the "Master" worksheet module:-



Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsMaster As Worksheet
Dim wsNew As Worksheet
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Intersect(Target, Range("B3:B" & Rows.Count)) Is Nothing Then Exit Sub

On Error Resume Next
Set wsNew = ThisWorkbook.Sheets(Target.Value)
If Not wsNew Is Nothing Then
MsgBox "A sheet for A/c No. " & Target.Value & " already exists. No new sheet will be added.", vbExclamation
Exit Sub
End If
Set wsMaster = ActiveSheet
Sheets("Template").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
With Sheets(ThisWorkbook.Sheets.Count)
.Name = Target.Value
End With
MsgBox "A new sheet is ready!", vbExclamation
Sheets("Master").Select
End Sub



This code will create a new sheet named after any new account number that you create. It will also warn you that, if an account number sheet already exists, no new sheet will be created but you will still be able to do your data entry for this account number.

The following code needs to be placed in a standard module:-


Sub TransferData()
Application.ScreenUpdating = False
Dim lRow As Long
Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("B3:B" & lRow)
MySheet = cell.Value
Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).ClearContents
Next cell
MsgBox "Data transfer completed!", vbExclamation
End Sub


This code transfers the data to each individual sheet. You will need to create a button on the "Master" sheet and assign the code (macro) to it.

I have attached a test work book for you to play with. I had to create a new one as the sample that you supplied created problems due to the code that you have in the "Master" sheet module (even after I deleted it). It left "residues" in the worksheets which played havoc with the new code. I would recommend that you create a brand new work book and start from scratch with the new above-mentioned codes. Discard your current work book.

In the attached test work book, please note the following:-

- I have created a "Template" sheet which the first code above uses to create the new sheets. This is simply based on the "Master" sheet. Make sure that you keep this "Template" sheet. You can at any time alter the Template to suit your needs.
- I did not include in the Template the tables shaded in blue that you have in the account number sheets of your sample work book. They vary in set out so I was not sure which one you would want to use. You can of course, as mentioned above, add this to the Template but, ideally, in a standard format. I'll leave this to you.
- The second code also clears the data entries from the "Master" sheet once the data is transferred to the relevant sheets.

I hope that this helps.

Cheerio,
vcoolio.

[Geeta2013]

Thank you. Will look through it today, and let you know my thoughts. muchas gracias!