1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data from various sheets onto a master

Discussion in 'VBA Macros' started by emmatm, Jul 23, 2017.

  1. emmatm

    emmatm Member

    Messages:
    64
    So I have 4 tabs on a workbook and I want to transfer data from all 4 tabs that are of a certain status which is shown in a set column in each tab onto a new tab within a new workbook, what's the easiest way to do this?
    I do not want to add the headers and I have very limited knowledge of VBA!!
  2. YasserKhalil

    YasserKhalil Active Member

    Messages:
    740
    Hello
    Can you post sample of your workbook?
  3. emmatm

    emmatm Member

    Messages:
    64
    Hi

    So I am trying to take the data from these 4 tabs:
    Projects
    Small Works
    Tech & Crate
    Tech Only
    And add it all onto tab 'ALL'
    Then where column O is 'SCHEDULED' I want this data to copy onto the Scheduled tab.
    Then were column O is 'COMPLETED' I want this data to copy onto the Completed tab.
    All other options to go onto the Progress tab.

    Attached Files:

  4. dan_l

    dan_l Active Member

    Messages:
    724
    this won't win points for style

    Code (vb):

    Sub movestuff()

      For Each ws In ThisWorkbook.Worksheets
      Select Case ws.Name
      Case "Projects", "Small Works", "Tech & Crate", "Tech Only"
      Call dowork(ws.Name)
      End Select
      Next ws

      Call movespecificvals

    End Sub


    Sub dowork(sWSName As String)
      Dim avData() As Variant
      Dim lRecordCount As Long
      Dim lvticker As Long
      Dim lhticker As Long

      avData = Sheets(sWSName).UsedRange.Value

      For lvticker = 2 To UBound(avData, 1)
      lRecordCount = Sheets("All").UsedRange.Rows.Count + 1
      For lhticker = 1 To UBound(avData, 2)
      Sheets("All").Cells(lRecordCount, lhticker).Value = avData(lvticker, lhticker)
      Next lhticker
      Next lvticker

    End Sub

    Sub movespecificvals()
      Dim avData() As Variant
      Dim lRecordCount As Long
      Dim lvticker As Long
      Dim lhticker As Long

      avData = Sheets("all").UsedRange.Value

      For lvticker = 1 To UBound(avData, 1)
      If UCase(avData(lvticker, 15)) = "SCHEDULED" Then
      lRecordCount = Sheets("Scheduled").UsedRange.Rows.Count + 1
      For lhticker = 1 To UBound(avData, 2)
      Sheets("Scheduled").Cells(lRecordCount, lhticker).Value = avData(lvticker, lhticker)
      Next lhticker
      ElseIf UCase(avData(lvticker, 15)) = "COMPLETED" Then
      lRecordCount = Sheets("Completed").UsedRange.Rows.Count + 1
      For lhticker = 1 To UBound(avData, 2)
      Sheets("Completed").Cells(lRecordCount, lhticker).Value = avData(lvticker, lhticker)
      Next lhticker
      End If
      Debug.Print avData(lvticker, 15)
      Next lvticker

       
    End Sub


     
    Thomas Kuriakose and emmatm like this.
  5. emmatm

    emmatm Member

    Messages:
    64
    Thank you dan,
    There seems to a problem, I have attached my file.
    Could you explain what I need to do and could you recommend a good starting point for me to learn vba language??
    BTW I have removed a tab called Smallworks, I am not sure if this is the problem! I did remove any reference to this that I could see in the code.

    Attached Files:

  6. dan_l

    dan_l Active Member

    Messages:
    724
    is it that your statuses say "sheduled" instead of "scheduled"?
  7. emmatm

    emmatm Member

    Messages:
    64
    Ah ok, I have amended those now to read 'scheduled'.

    Still no results, I have also changed 15 to 16 as the status is now in row P not O

    Attached Files:

  8. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!
    Transfer means move, not copy, isn't it? Because of the topic title...
    That means:
    a) you don't want the headers to be copied
    b) you don't want to manually copy the headers
    You tried to mean column P, right?

    And one thing more:
    Will the target worksheets contain data at move/copy time? If so, what should happen? Add at bottom or replace?

    Regards!
    Last edited: Jul 24, 2017
  9. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!

    Give a look at the uploaded file. I didn't touch anything of the VBA code (only in Module1), except for 2 lines in worksheet Progress, and added the following code to Módulo1.
    Code (vb):
    Option Explicit

    Sub DifferenceBetweenEngineersAndArchiects()
        '
       ' constants
       Const ksWSSource = ",Projects,Small Works,Tech & Crate,Tech Only"
        Const ksWSTarget = ",All,Scheduled,Completed,Progress"
        Const kiFilter = 16
        Const ksCriteria = ",,SCHEDULED,COMPLETED,"
        '
       ' declarations
       Dim sSrc() As String, sTgt() As String, sCriteria() As String
        Dim iSrc() As Integer, iTgt() As Integer
        Dim I As Integer, J As Integer, b As Boolean
        '
       ' start
       '  arrays
       sSrc = Split(ksWSSource, ",")
        sTgt = Split(ksWSTarget, ",")
        sCriteria = Split(ksCriteria, ",")
        ReDim iSrc(UBound(sSrc))
        ReDim iTgt(UBound(sTgt))
        '  titles
       For I = 1 To UBound(sTgt)
            iTgt(I) = 1
            With Worksheets(sTgt(I))
                .Cells.Clear
                Worksheets(sSrc(1)).Rows(1).Copy .Rows(1)
            End With
        Next I
        '
       ' process
       For I = 1 To UBound(sSrc)
            ' existent sheet
           If Not WorksheetFunction.IsErr(Evaluate("'" & sSrc(I) & "'!A1")) Then
                ' copy all to all
               CopyingFilteredRows sTgt(1), sSrc(I), 0, "*", "*"
                ' copy scheduled to scheduled
               CopyingFilteredRows sTgt(2), sSrc(I), kiFilter, sCriteria(2), sCriteria(2)
                ' copy completed to completed
               CopyingFilteredRows sTgt(3), sSrc(I), kiFilter, sCriteria(3), sCriteria(3)
                ' copy no scheduled & no completed to progress
               CopyingFilteredRows sTgt(4), sSrc(I), kiFilter, "<>" & sCriteria(2), "<>" & sCriteria(3)
            Else
                MsgBox "Unable to find worksheet " & sSrc(I), vbOKOnly + vbCritical, "Warning"
            End If
        Next I
        '
       ' end
       '
    End Sub

    Private Sub CopyingFilteredRows(psTgt As String, psSrc As String, piFilter As Integer, psCriteria1 As String, psCriteria2 As String)
        ' constants
       ' declarations
       Dim lRow As Long
        ' start
       Application.ScreenUpdating = False
        ' process
       With Worksheets(psSrc)
            lRow = Worksheets(psTgt).[A1].End(xlDown).End(xlDown).End(xlUp).Row
            If piFilter > 0 Then .Cells.AutoFilter Field:=piFilter, Criteria1:=psCriteria1, Operator:=xlAnd, Criteria2:=psCriteria2
            If .[A1].Offset(1, 0).Value <> "" And .[A1].Offset(1, 0).End(xlDown).Value <> "" Then _
                Range(.[A1].Offset(1, 0).EntireRow, .[A1].Offset(1, 0).End(xlDown).EntireRow).Copy Worksheets(psTgt).Rows(lRow + 1)
            If piFilter > 0 Then .ShowAllData
        End With
        ' end
       Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub
    It does the job using filter and copies (for the time being) data once for each input worksheet in order to obtain a faster process, instead of processing each line of each worksheet which would demand a lot of more time depending on the no. of rows on your worksheets.

    You have to run the macro DifferenceBetweenEngineersAndArchiects. Just advise if any issue.

    Regards!

    Attached Files:

    Thomas Kuriakose likes this.
  10. dan_l

    dan_l Active Member

    Messages:
    724
    smug much?
  11. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    @dan_l
    Hi!
    Neither one of them.
    Regards!
  12. emmatm

    emmatm Member

    Messages:
    64
    H SirJB7,

    This works well, just 2 questions.
    1. initially I had a 'small works' sheet but I had since deleted that, the code is still looking for a small works tab.
    2. I think because 'tech only' tab has columns hidden, when this data is copying over to the ALL tab the status are showing in column P which is what I want but for sheets 'scheduled', 'complete' & 'progress' status is showing in columns I.

    Thank you
  13. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!

    Firstly 2 answers.

    1) This code
    Code (vb):

        Const ksWSSource = ",Projects,Small Works,Tech & Crate,Tech Only"
        Const ksWSTarget = ",All,Scheduled,Completed,Progress"
    holds the list of source worksheets (1st line) and the list of target worksheets (2nd line).
    I assumed that target worksheets will always exists and as you omitted Small Works I prevented from non existent source worksheets with this line:
    Code (vb):

            If Not WorksheetFunction.IsErr(Evaluate("'" & sSrc(I) & "'!A1")) Then
    So you can either remove it from the constant ksWSSource or just leave it for whenever you add it again in the future. With the same criteria you can add any new source worksheets separated by comma.

    2) Yes, you're right. I didn't notice that there were worksheets with hidden columns. This will force to change the code substantially unless you don't mind if either you manually or the process automatically unhide the hidden columns.

    At last a reminder of old unanswered questions:

    Regards!


    Regards!
    emmatm likes this.
  14. emmatm

    emmatm Member

    Messages:
    64
    Thank you for your help, I removed the small works as instructed.

    Your old unanswered questions:
    I should have used the word 'copy' not 'transfer', as transfer does mean to move!!
    I did not want to add the headers from each tab but wanted only one header on the new tab.
    Finally column P was originally O but since added another column of data.
  15. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!
    So let us negotiate the last point. Hidden columns. Any suggestions that will make me work as little as possible and that don't make you suffer a lot (a bit is acceptable!)?
    Regards!
  16. emmatm

    emmatm Member

    Messages:
    64
    So I have been asked to now have the 4 tabs of new data on a new workbook instead:
    ALL
    Scheduled
    Complete
    Progress

    Do you think this is something you can show me how to do?
    I can upload the additional workbook if you think this is possible?

    And to answer the question above could you code in to unhide the columns on this tab before the data is transferred and then hide the columns when it is complete? I know I do ask a lot!
  17. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!

    At last somebody asked something for improving usefulness; target worksheets in the same workbork isn't the best & handy idea.

    Let us do something,:
    a) while I adjust the code for a generic workbook, you upload the additional one just to check worksheet's structure and format.
    b) this output should add rows to existing ones, replace them or create new workbook?
    c) that stuff of unhiding and rehiding columns... hmmm... :oops:... it requires a six-pack of Carlsbergs!
    I have a doubt... may I?... what do you understand by negotiate?

    Regards!
  18. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!

    File updated and uploaded again. This is the adjusted VBA code:
    Code (vb):
    Option Explicit

    Sub DifferenceBetweenEngineersAndArchitects()
        '
       ' constants
       Const ksWSSource = ",Projects,Small Works,Tech & Crate,Tech Only"
        Const ksWSTarget = ",All,Scheduled,Completed,Progress"
        Const ksWBTarget = "JLL MAC DATA FILE - #.xlsx"
        Const kiFilter = 16
        Const ksCriteria = ",,SCHEDULED,COMPLETED,"
        '
       ' declarations
       Dim wbSrc As Workbook, wbTgt As Workbook
        Dim sSrc() As String, sTgt() As String, sCriteria() As String
        Dim iSrc() As Integer, iTgt() As Integer
        Dim I As Integer, J As Integer, b As Boolean, A As String
        '
       ' start
       '  arrays
       sSrc = Split(ksWSSource, ",")
        sTgt = Split(ksWSTarget, ",")
        sCriteria = Split(ksCriteria, ",")
        ReDim iSrc(UBound(sSrc))
        ReDim iTgt(UBound(sTgt))
        '  wbs
       Set wbSrc = ThisWorkbook
        wbSrc.Activate
        Workbooks.Add
        A = Replace(ksWBTarget, "#", Format(Now(), "yyyy-mm-dd hh.mm.ss"))
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & A
        Set wbTgt = ActiveWorkbook
        With wbTgt
            For I = 1 To UBound(sTgt)
                If I > .Worksheets.Count Then .Worksheets.Add , .Worksheets(.Worksheets.Count)
                .Worksheets(I).Name = sTgt(I)
            Next I
        End With
        '  titles
       For I = 1 To UBound(sTgt)
            iTgt(I) = 1
            With wbTgt.Worksheets(sTgt(I))
                .Cells.Clear
                wbSrc.Worksheets(sSrc(1)).Rows(1).Copy .Rows(1)
            End With
        Next I
        '
       ' process
       wbSrc.Activate
        For I = 1 To UBound(sSrc)
            ' existent sheet
           If Not WorksheetFunction.IsErr(Evaluate("'" & sSrc(I) & "'!A1")) Then
                ' copy all to all
               CopyingFilteredRows wbTgt, sTgt(1), sSrc(I), 0, "*", "*"
                ' copy scheduled to scheduled
               CopyingFilteredRows wbTgt, sTgt(2), sSrc(I), kiFilter, sCriteria(2), sCriteria(2)
                ' copy completed to completed
               CopyingFilteredRows wbTgt, sTgt(3), sSrc(I), kiFilter, sCriteria(3), sCriteria(3)
                ' copy no scheduled & no completed to progress
               CopyingFilteredRows wbTgt, sTgt(4), sSrc(I), kiFilter, "<>" & sCriteria(2), "<>" & sCriteria(3)
            Else
                MsgBox "Unable to find worksheet " & sSrc(I), vbOKOnly + vbCritical, "Warning"
            End If
        Next I
        '
       ' end
       '  save & reposition
       wbTgt.Save
        '  beep (thanks to Marc L at chandoo.org)
       MarcLBeepDemo
        '  wbs
       Set wbSrc = Nothing
        Set wbTgt = Nothing
        '
    End Sub

    Private Sub CopyingFilteredRows(pwbTgt As Workbook, psTgt As String, psSrc As String, _
                                    piFilter As Integer, psCriteria1 As String, psCriteria2 As String)
        ' constants
       ' declarations
       Dim lRow As Long
        ' start
       With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        ' process
       With Worksheets(psSrc)
            lRow = Worksheets(psTgt).[A1].End(xlDown).End(xlDown).End(xlUp).Row
            If piFilter > 0 Then .Cells.AutoFilter Field:=piFilter, Criteria1:=psCriteria1, Operator:=xlAnd, Criteria2:=psCriteria2
            If .[A1].Offset(1, 0).Value <> "" And .[A1].Offset(1, 0).End(xlDown).Value <> "" Then _
                Range(.[A1].Offset(1, 0).EntireRow, .[A1].Offset(1, 0).End(xlDown).EntireRow).Copy pwbTgt.Worksheets(psTgt).Rows(lRow + 1)
            If piFilter > 0 Then .ShowAllData
        End With
        ' end
       With Application
            .CutCopyMode = False
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
    End Sub
    For the time being it does nothing about hidden columns (I'm still negotiating), creates new workbooks in same path with same name plus date & time.

    And about this
    I followed Nike's motto, just did it. Feel free to ask anything you don't understand.

    Regards!

    PS: thanks to @Marc L for the beep snippet... if you don't like it just replace it by the old tiny Beep, or remove it.

    Attached Files:

    Thomas Kuriakose likes this.
  19. emmatm

    emmatm Member

    Messages:
    64
    b) each time the macro is run it should replace to the relevant tab
    c) you may have a better solution seeing as you are the genius here??

    Negotiate.......you would like something in return! lets hope this doesn't start to get very rude and awkward:p

    I will upload the file later.

    Thank you SirJB7
  20. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!
    Maybe redundant but... same file or date & time stamped as for now?
    Check this:
    http://chandoo.org/forum/members/sirjb7.1781/
    Information tab, quote from that guy Albert.
    Rude & awkard? Nothing farthest from my thoughts... (unless 'til I get any proposal which I've never expected... never say never... again...)
    I'm very lazy (I tend to say that I try to make the most environmentally friendly use of labor resources... if we talk about those of myself), so with haven't to work neither hard nor much I'm done! :DD
    Regards!
    Last edited: Jul 26, 2017
  21. Marc L

    Marc L Excel Ninja

    Messages:
    3,175
    Hi !

    I'll catch half of the Carlsberg six-pack ! :DD
  22. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Marc L!
    Pourquoi pas, mon ami? (Why not, my friend?) Be my guest... But I think we'll have to wait & see what does emmatm says... I kept on wondering about a counter proposal... ;)
    But if you check the code you'll see that you'd be in disadvantage if you settle only for that...
    Regards!
    Last edited: Jul 26, 2017
    Marc L likes this.
  23. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!

    As a sign of goodwill here's the updated code and uploaded file.
    Code (vb):
    Option Explicit

    Sub DifferenceBetweenEngineersAndArchitects()
        '
       ' constants
       Const ksWSSource = ",Projects,Small Works,Tech & Crate,Tech Only"
        Const ksWSTarget = ",All,Scheduled,Completed,Progress"
        Const ksWBTarget = "JLL MAC DATA FILE - #.xlsx"
        Const kiFilter = 16
        Const ksCriteria = ",,SCHEDULED,COMPLETED,"
        '
       ' declarations
       Dim wbSrc As Workbook, wbTgt As Workbook
        Dim sSrc() As String, sTgt() As String, sCriteria() As String
        Dim iSrc() As Integer, iTgt() As Integer
        Dim bSrc() As Boolean
        Dim I As Integer, J As Integer, b As Boolean, bSave As Boolean, A As String
        '
       ' start
       '  arrays
       sSrc = Split(ksWSSource, ",")
        sTgt = Split(ksWSTarget, ",")
        sCriteria = Split(ksCriteria, ",")
        ReDim iSrc(UBound(sSrc))
        ReDim iTgt(UBound(sTgt))
        '  wbs
       '  source
       Set wbSrc = ThisWorkbook
        With wbSrc
            .Activate
            bSave = .Saved
        End With
        '  target
       Workbooks.Add
        A = Replace(ksWBTarget, "#", Format(Now(), "yyyy-mm-dd hh.mm.ss"))
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & A
        Set wbTgt = ActiveWorkbook
        With wbTgt
            For I = 1 To UBound(sTgt)
                If I > .Worksheets.Count Then .Worksheets.Add , .Worksheets(.Worksheets.Count)
                .Worksheets(I).Name = sTgt(I)
            Next I
        End With
        '  titles
       For I = 1 To UBound(sTgt)
            iTgt(I) = 1
            With wbTgt.Worksheets(sTgt(I))
                .Cells.Clear
                wbSrc.Worksheets(sSrc(1)).Rows(1).Copy .Rows(1)
            End With
        Next I
        '  hidden cols - part I (aka six-pack of Carlsberg)
       With wbSrc
            .Activate
            I = .Worksheets(sSrc(1)).[A1].End(xlToRight).Column
            ReDim bSrc(UBound(sSrc), I)
            For I = 1 To UBound(sSrc)
                ' existent sheet
               If Not WorksheetFunction.IsErr(Evaluate("'" & sSrc(I) & "'!A1")) Then
                    With .Worksheets(sSrc(I))
                      For J = 1 To UBound(bSrc, 2)
                            bSrc(I, J) = .Columns(J).Hidden
                            .Columns(J).Hidden = False
                        Next J
                    End With
                End If
            Next I
        End With
        '
       ' process
       wbSrc.Activate
        For I = 1 To UBound(sSrc)
            ' existent sheet
           If Not WorksheetFunction.IsErr(Evaluate("'" & sSrc(I) & "'!A1")) Then
                ' copy all to all
               CopyingFilteredRows wbTgt, sTgt(1), sSrc(I), 0, "*", "*"
                ' copy scheduled to scheduled
               CopyingFilteredRows wbTgt, sTgt(2), sSrc(I), kiFilter, sCriteria(2), sCriteria(2)
                ' copy completed to completed
               CopyingFilteredRows wbTgt, sTgt(3), sSrc(I), kiFilter, sCriteria(3), sCriteria(3)
                ' copy no scheduled & no completed to progress
               CopyingFilteredRows wbTgt, sTgt(4), sSrc(I), kiFilter, "<>" & sCriteria(2), "<>" & sCriteria(3)
            Else
                MsgBox "Unable to find worksheet " & sSrc(I), vbOKOnly + vbCritical, "Warning"
            End If
        Next I
        '
       ' end
       '  hidden cols - part II (aka another six-pack of Carlsberg)... or...
       With wbSrc
            For I = 1 To UBound(sSrc)
                ' existent sheet
               If Not WorksheetFunction.IsErr(Evaluate("'" & sSrc(I) & "'!A1")) Then
                    With .Worksheets(sSrc(I))
                        For J = 1 To UBound(bSrc, 2)
                            .Columns(J).Hidden = bSrc(I, J)
                        Next J
                    End With
                End If
            Next I
        End With
        '  save & reposition
       wbSrc.Saved = bSave
        wbTgt.Save
        '  beep (thanks to Marc L at chandoo.org)
       MarcLBeepDemo
        MsgBox "Workbook has been created: " & A, vbInformation + vbOKOnly, "End process"
        '  wbs
       Set wbSrc = Nothing
        Set wbTgt = Nothing
        '
    End Sub

    Private Sub CopyingFilteredRows(pwbTgt As Workbook, psTgt As String, psSrc As String, _
                                    piFilter As Integer, psCriteria1 As String, psCriteria2 As String)
        ' constants
       ' declarations
       Dim lRow As Long
        ' start
       With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        ' process
       With Worksheets(psSrc)
            lRow = Worksheets(psTgt).[A1].End(xlDown).End(xlDown).End(xlUp).Row
            If piFilter > 0 Then .Cells.AutoFilter Field:=piFilter, Criteria1:=psCriteria1, Operator:=xlAnd, Criteria2:=psCriteria2
            If .[A1].Offset(1, 0).Value <> "" And .[A1].Offset(1, 0).End(xlDown).Value <> "" Then _
                Range(.[A1].Offset(1, 0).EntireRow, .[A1].Offset(1, 0).End(xlDown).EntireRow).Copy pwbTgt.Worksheets(psTgt).Rows(lRow + 1)
            If piFilter > 0 Then .ShowAllData
        End With
        ' end
       With Application
            .CutCopyMode = False
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
    End Sub
    Now, the ball is on your side of the field.
    Regards!

    Attached Files:

    Thomas Kuriakose likes this.
  24. emmatm

    emmatm Member

    Messages:
    64
    Hello again,

    So thank you very much for this, it is greatly appreciated. Nice touch with the Carlsberg, you can look forward to this soon :DD, just let me know how I can get this to you??

    I think you might want to kill me but I have re-named the file and attached a new version 'MAC TRACKER', following the same lines I need to copy and paste data from tabs 'projects, tech & crate, tech only, crate only & OH' un-hiding any columns to workbook 'MAC ADMIN' overwriting with new data on tabs 'ALL, Scheduled & complete'.

    When the JLL MAC ID's file is received from someone else this is copied onto the first tab of the tracker, can you code for any new rows to be added into tabs 'projects, tech & crate, tech only, crate only & OH' from row C-Q onto the relevant tab decided by the project type in column A onto rows A-O (hope that makes sense!) This will need to be the first action taken before moving the data onto new tabs as per above.

    So every day when the new JLL MAC ID file is received this process will need to be done to update both spreadsheets and overwrite all the data as statuses etc. will change.

    Later I need to create a weekly client file that formats the 3 tabs on the MAC ADMIN file by location and project type in a pretty table to review directly with the client.

    I am hoping that the code above can be copied with a little bit of tweaking?

    Phew - I think you will deserve a beer after all of that!

    Attached Files:

  25. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, emmatm!

    If we weren't in a writing platform I would say "Before speaking I would like to say a few words" and they are:
    I highly appreciate your gesture, I'm sure Marc L will join me, but it's not necessary, thanks a lot, indeed it'll be a bit difficult as I'm from the Southern Cone, hahaha.

    Now I'll begin.

    Have I told you lately that I... (ooops... nop, this is an older song by Rod Stewart, Tom Jones too, and many more)... no, I haven't, but you're absolutely right about your thoughts about mine's.

    Regarding the workbook structure it seems to be quite similar to the original:
    - source file with a list of worksheets
    - data filtered by one single column
    - target file with a list of worksheets
    - unhiding columns by Carlsberg

    If these are the only workbooks where you'll have to do the process of filtering and copying maybe we can arrive to a single procedure shared by both of them.

    What I don't understand is the actual process of distributing and updating both files. Would you please elaborate? Describe precisely things like:
    - "When the JLL MAC ID's file is received from someone else", which file? a workbook?, a worksheet?
    - "this is copied onto the first tab of the tracker", new operation?
    - "can you code for any new rows to be added into tabs 'projects, tech & crate, tech only, crate only & OH' from row C-Q <rows with letters? I'd paid for viewing that!> onto the relevant tab decided by the project type in column A onto rows A-O" (hope that makes sense! <no, it doesn't>)"
    - "This will need to be the first action taken before moving the data onto new tabs as per above.", moving? weren't we copying?
    - "So every day when the new JLL MAC ID file is received this process will need to be done to update both spreadsheets and overwrite all the data as statuses etc. will change.", which file?, how do you update each one? (maybe this gets clear after the previous clarifications required)
    - "Later I need to create a weekly client file that formats the 3 tabs on the MAC ADMIN file by location and project type in a pretty table to review directly with the client.", hahaha, and I'm dressed in read & white, wear a beard and a bonnet with pompom...
    "Will" denotes future time, "a" seems to be in singular, but what it really offends me is "after that". Now, try to figure out how to come back from this situacion.:mad:

    Regards!

Share This Page