Conditionally copy col values from one sheet to master sheet Closed

Question

I have 3 workssheets, Master , Billing and EA.I'm trying 2 diff things:
1. update the master sheet when either of the Offer or EA sheets are updated/changed.
2. if  any particular column in Billing/EA worksheet is changed only that value (updated column)needs to be copied to Master Employee sheet.


Hi i need some help with a workbook i’m working on and i cant figure out how or if what i want to do can be done. 

the workbook is to track Employment details billing  and offers. 

i have a master sheet where i enter the name, start date, end date, client and etc… 

then i have a sheet with billing and offer details for all candidates and the few same columns from the master sheet. 

is it possible to enter the information on the master sheet and have it automatically transferred to the different sub sheets? or enter the info on the sub sheets and automatically transferred to the master sheet? 

how can i accomplish this? 

CAN Somebody please help with a macro?

avittal · Answer

anyone Help?!

vcoolio · Answer

Hello Avittal,

The following code, placed in a standard module and assigned to a button, may help:-

Sub TransferData()

Dim ar As Variant, i As Integer

ar = [{"Billing","EA";"Billing","EA"}]

Application.ScreenUpdating = False

For i = 1 To UBound(ar, 2)
      Sheets(ar(1, i)).UsedRange.Offset(1).ClearContents
        With Sheet1
            .AutoFilterMode = False
                With Range("H1", Range("H" & Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(2, i)
                    .Offset(1).EntireRow.Copy Sheets(ar(1, i)).Range("A" & Rows.Count).End(3)(2)
                    ActiveSheet.AutoFilterMode = False
                    Sheets(ar(1, i)).Columns.AutoFit
                End With
        End With
    Next i
    
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "All done!", vbExclamation

End Sub

The code assumes that you have data from Columns A - H and a variable amount of rows in the Master sheet. Column H has the criteria (Billing or EA).

Once you have entered the data as required and click on the button, the relevant rows of data will be transferred to the relevant individual sheet (Billing or EA).

Following is the link to a little sample that I have prepared for you:-

https://www.dropbox.com/s/dx8d6nv9f05ornx/Avittal%28master%20sht%20to%20multi%20shts%29.xlsm?dl=0

In the sample, you will notice that there is a data validation drop down in each cell in Column H just to help in making entries a little quicker. Select a few then click on the "RUN" button. You will then see the relevant rows of data in their respective sheets.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Avittal,

I now assume that you don't want the data transferred from the Master sheet to the individual sheets as per my sample above but the other way around: individual sheets to Master sheet. 

There now also appears to not be a "Billings" sheet but an "Offers" sheet.

Looking at your sample work book, there isn't any consistency in the set out of any of the sheets. It is a jumble of varying headings with data below in all sheets. This will make a solution for you a difficult proposition.

Can all the work sheets be set out in the same format? For example: can the "Offers" and "EA" work sheets be set out the same, column-wise, as the Master sheet with any required additional columns placed after the main (or more important columns)? 
This will make for a more superior, faster and efficient solution for you. The way the sheets are currently set out would require a mass of formulae or VBA coding to effectively do what you would like which in turn could make the whole procedure very slow (assuming that your data sets are large).

Please advise.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Avittal,

To have an ordered, legible and symmetric workbook of any description is a basic book-keeping rule.

Below is a link to another sample that I have prepared for you:_

https://www.dropbox.com/s/v4wcishr7u13dlp/Avittal%28multi%20shts%20to%20master%2Cworkbook_sheetchange%29.xlsm?dl=0

Its just an example so the rest is up to you.

You'll see that each sheet has headings in the same order as the Master sheet. The columns with the "more important data" I have placed in bold font and are headed as Heading1. The "less important data" columns are in plain font and are headed as Heading2.
You'll notice that Column F in each source sheet has the heading Update(OK). This column is basically used to confirm that any new or updated entries that you make in any cell on any sheet is confirmed as being OK to transfer to sheet1 (Master). So, for example, if you update any cell in say row 5 and then enter OK on the same row in Column F to confirm the entry then the Master sheet will be updated with the new data. In the sample, try changing any cell in the data sets of the two source sheets, enter OK in the relevant cell in Column F and then click away (or press enter or down arrow). Go to the Master sheet and you'll notice that the data that is already there has been updated.

Of course, set out your work book as you like but keep it ordered in each sheet.

Following is the code that does the work:-


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Columns("F")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(1).ClearContents

For Each Sh In Worksheets
    If Sh.Name <> "Master" Then
        If Target.Value = "OK" Then
            Sh.UsedRange.Offset(1).Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
              End If
        End If
Next Sh

Sheet1.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

It is a Workbook_SheetChange event and will work from any sheet.

To implement the code:-

- Press Alt + F11. You will then be in the VB Editor.
- Over to the left, double click on "ThisWorkbook".
- Paste the above code into the big white field.

Go back to your work sheets and do some testing but please test the code in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.

P.S. Avittal, just an addition to the code.

Directly above this line of code:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

place this line of code:-

Option Compare Text

This will make the Column F criteria case insensitive which means that if you type in "ok" or "OK" the code will work regardless.

vcoolio · Answer

Hello Hitrus87,

As you would be aware, this thread, started by Avittal, is still open and yet to be resolved.

Please do not hijack other Posters' open threads with a query which is dissimilar to the original Poster's.

Hence, please start your own thread with a clear and precise explanation of that which you wish to achieve including supplying a sample of your actual work book rather than images. 

You can upload a sample of your work book to a free file sharing site such as Drop Box, ge.tt or Sendspace and then post the link to your file in your opening post. Please use dummy data in the sample.

We trust that you understand.

Cheerio,
vcoolio.

Conditionally copy col values from one sheet to master sheet

5 responses

Subscribe To Our Newsletter!