Conditionally copy col values from one sheet to master sheet

Closed
Report
Posts
4
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
June 26, 2017
-
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017
-
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?

5 replies

Posts
4
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
June 26, 2017

anyone Help?!
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
Posts
4
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
June 26, 2017

Hello vcoolio,

Thanks for responding.your solution works. what i'm looking for is for a particular candidate name if there are additions/changes in EA/Offer sheets( for eg, the address is newly changed/edited) only the changed/updated value should be updated/inserted in my master worksheet.
im attaching my sample worksheet with dummy data.
in this sheet i have color coded 2 candidate names they have diff address in master and EA/offer sheets,
the macro should look for any cahnges and update only the changed value in master sheet.

https://www.dropbox.com/s/26ihye2c23u3x20/Workbook1.xlsx?dl=0

pls help...
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
Posts
4
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
June 26, 2017

Hello vcoolio,
Thanks for the reply, Yes all the worksheets can be set in the same format.For example: the "Offers" and "EA" work sheets can be set out the same, column-wise, as the Master sheet with any required additional columns placed after the main (or more important columns).

Please suggest what needs to be done next.
,
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017

Hi Everyone,

I want to create a VBA in which it will copy the template Sheet "5" and rename it according to the range i give but that range will not necessarily start from first row the range is in E column of DATA sheet Tab which has also the Name in column A and i also want that the name shall be updated in template sheet as the sheet is created .

First attached is the screen shot of the template
in which sheet name is "5" i want to copy this sheet as well change the name of the sheet as well the name of person


Next is the source of name of person and also the sheet name i want to create. also is should be dynamic and should create the sheets for the numbers given in the range column.


thanks In advance
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017

I have not intended to do it.. please dis consider my question... still want to clear don't feel offended.

Thanks.