Copy data from various sheets onto a master

[emmatm]

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!!

 

[YasserKhalil]

Hello
Can you post sample of your workbook?

 

[emmatm]

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.

 

[dan_l]

this won't win points for style

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

 

[emmatm]

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.

 

[dan_l]

is it that your statuses say "sheduled" instead of "scheduled"?

 

[emmatm]

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

 

[SirJB7]

Hi, emmatm!

So I have 4 tabs on a workbook and I want to transfer data from all 4 tabs

Transfer means move, not copy, isn't it? Because of the topic title...

I do not want to add the headers and I have very limited knowledge of VBA!!

That means:
a) you don't want the headers to be copied
b) you don't want to manually copy the headers

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.

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!

 

[SirJB7]

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.

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!

 

[dan_l]

You have to run the macro DifferenceBetweenEngineersAndArchiects.

Regards!

smug much?

 

[SirJB7]

@dan_l
Hi!
Neither one of them.
Regards!

 

[emmatm]

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

 

[SirJB7]

Hi, emmatm!

Firstly 2 answers.

1) This code

    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:

        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:

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!


Regards!

 

[emmatm]

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.

 

[SirJB7]

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!

 

[emmatm]

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!

 

[SirJB7]

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... ... it requires a six-pack of Carlsbergs!

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!

I have a doubt... may I?... what do you understand by negotiate?

Regards!

 

[SirJB7]

Hi, emmatm!

File updated and uploaded again. This is the adjusted VBA code:

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

Do you think this is something you can show me how to do?

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.

 

[emmatm]

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

I will upload the file later.

Thank you SirJB7

 

[SirJB7]

Hi, emmatm!

b) each time the macro is run it should replace to the relevant tab

Maybe redundant but... same file or date & time stamped as for now?

c) you may have a better solution seeing as you are the genius here??

Check this:
http://chandoo.org/forum/members/sirjb7.1781/
Information tab, quote from that guy Albert.

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

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!
Regards!

 

[Marc L]

PS: thanks to @Marc L for the beep snippet...

Hi !

I'll catch half of the Carlsberg six-pack !
​

 

[SirJB7]

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!

 

[SirJB7]

Hi, emmatm!

As a sign of goodwill here's the updated code and uploaded file.

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!

 

[emmatm]

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 , 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!

 

[SirJB7]

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:

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

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...

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

"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.

Regards!