I am new to VBA therefore needs help.

I have three worksheet say CompanyA, Supplier1 & Supplier2.

In CompanyA it has headers:
Date Company Payment Amount Invoice No. Comments

I update these details when I make payments to Supplier1 & 2, then I have to go and update the two Suppliers' worksheet.

Can anyone help me in VBA so that I just use a VBA comment or button to update these data automatically without having to copy and pasting myself.

Please feel free if anyone knows an easier than what I'm thinking.

OK well going on the assumption that the other sheets have the same headers, and you wish to enter the same data into both, you could use something like this:

Sub UpdateSuppliers()
Dim iRo As Integer, NumCols As Integer, NumRow1 As Integer, NumRow2 As Integer
Dim CompSht As Worksheet, Sup1Sht As Worksheet, Sup2Sht As Worksheet
Set CompSht = Sheets("CompanyA")
Set Sup1Sht = Sheets("Supplier1")
Set Sup2Sht = Sheets("Supplier2")
NumRow1 = Sup1Sht.Cells(Sup1Sht.Rows.Count, 1).End(xlUp).Row + 1
NumRow2 = Sup2Sht.Cells(Sup2Sht.Rows.Count, 1).End(xlUp).Row + 1
If ActiveWorkbook.ActiveSheet <> CompSht Then Exit Sub
iRo = ActiveCell.Row
NumCols = 6

For i = 1 To NumCols
    'Input data...
    Sup1Sht.Cells(NumRow1, i).Value = CompSht.Cells(iRo, i).Value
    Sup2Sht.Cells(NumRow2, i).Value = CompSht.Cells(iRo, i).Value
Next
End Sub

Add a button to your first sheet (or better still assign a keyboard shortcut) to this macro. When run, it will take the row you are currently on and input the data into the other 2 sheets. This is a very basic macro, if you would want it to only put it to one of the sheets, based on the company for example, or any other intricacies, can easily be done with some if statements. If you want to know how to do something extra, just ask. HTH, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

if you would want it to only put it to one of the sheets, based on the company for example, or any other intricacies, can easily be done with some if statements. If you want to know how to do something extra, just ask

Thank Dai

What I'd prefer is to transfer every data in workbook CompanyA that relates to Supplier1 to workbook Supplier1 and all those for Supplier2 to that Supplier2 workbook. Also as a beginner I'd prefer if you give me the steps of how assign this macro to a button, if it's not asking too much.

Thank you very much.

Sonny

OK first off, assigning a macro...

In Excel go to Insert>Shapes and pick a shape and draw it somewhere
Right-click the shape you have drawn and you will see an 'Assign a Macro' option, click that and choose your macro from the list, click OK
If you want a keyboard short-cut instead or as well, in Excel hit ALT+F8, this will bring up the Macros window, can also be found under Tools>Macros>Macros in 2003 and earlier or View>Macros in 2007 onwards
Click the 'Options' button and assign whatever key you want to use to it, then click OK
Note, most Ctrl shortcuts already have a function, e.g. Ctrl+A is select all, Ctrl+P is print, Ctrl+X, Ctrl+C, Ctrl+V are cut, copy, paste respectively, and the list goes on, so I recommend you don't use any of these commands, I would use Ctrl+Shift+(Any key).


As for the separating of the suppliers, you will need to build a list of what relates to supplier 1 and supplier 2, then use that list to find which one the current row relates to. This example assumes it's the Company name (Column B) that decides which it belongs to:

Sub UpdateSuppliers()
Const Supplier1Companies as String = "{ABC Ltd}{MNO Ltd}{XYZ Ltd}"
Const Supplier2Companies as String = "{DEF Ltd}{PQR Ltd}{XYZ Ltd}"

Dim iRo As Integer, NumCols As Integer, NumRow1 As Integer, NumRow2 As Integer
Dim CompSht As Worksheet, Sup1Sht As Worksheet, Sup2Sht As Worksheet
Set CompSht = Sheets("CompanyA")
Set Sup1Sht = Sheets("Supplier1")
Set Sup2Sht = Sheets("Supplier2")
NumRow1 = Sup1Sht.Cells(Sup1Sht.Rows.Count, 1).End(xlUp).Row + 1
NumRow2 = Sup2Sht.Cells(Sup2Sht.Rows.Count, 1).End(xlUp).Row + 1
If ActiveWorkbook.ActiveSheet <> CompSht Then Exit Sub
iRo = ActiveCell.Row
NumCols = 6

If InStr(1, Supplier1Companies, "{" & CompSht.Cells(iRo, 2).Value & "}") > 0 Then
    For i = 1 To NumCols
        'Input data...
        Sup1Sht.Cells(NumRow1, i).Value = CompSht.Cells(iRo, i).Value
    Next
End If

If InStr(1, Supplier2Companies, "{" & CompSht.Cells(iRo, 2).Value & "}") > 0 Then
    For i = 1 To NumCols
        'Input data...
        Sup2Sht.Cells(NumRow2, i).Value = CompSht.Cells(iRo, i).Value
    Next
End If

End Sub

That should do the trick. If in this example you had ABC Ltd on the line, it would go only to Supplier 1 sheet, if you had DEF Ltd, will only go to Supplier 2 sheet, if you had XYZ Ltd, it would go to both. As always, ask if you need to follow up on any of this.

Regards, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Thanks I'll try and get back to you. You've been a great help.