VBA Transpose and Move to a table

[PaulOPTC]

Good Afternoon,

This is a sample Profit and loss report by customer in Excel.


What I am looking to do is take row 5 (That being the Job Names)
and put the "Total Expenses" and "Total Other Expenses" into a table.

My issue is that I will not know how large the sheet is, so I will need ALL of the data on all of the selected rows (as of now its about 1000-1500 columns)
and "Total Expenses" and "Total Other Expenses" Could be on completely different rows then their current rows of 13 and 19.
Eventually there will be a line item that also says "Payroll Expenses" But I do not know what line that will be on.


I was trying some If statements, to then find the word and transpose it.

or trying an index and match to find the word and then get the row and then transpose it,

Neither has worked for what I am looking to do.




The table would look like: And it would be on the sheet "QB Table" starting in A1

Job Number	QB Job Name	Expenses	Other Expenses	Total Labor	Total Expenses
=VALUE(CLEAN((LEFT([@[QB Job Name]],5))))	This will be taken from Row 5	From "Total Expenses"	from "Total Other Expenses"	from "Payroll Expenses"	=[@Expenses]+[@[Other Expenses]]

"Not Specified" and "Total" are not needed and have no relevance and wont be on this table

And all jobs will start with a 4 digit job number (eventually it will be a 5 digit number so I have been doing value and then clean to get rid of the space)

and any blanks are not needed, but they don't really bother me.


_________________________________________________________________________________________________________________________________________________________________________

My Final plan for this would be to have the reports sent to me,

I can open them up, delete all the current data, and then auto populate QB Table with the new updated data.

QB table will be a table that other sheets will reference.


Any help you can provide would be appreciated!

Thank you!

 

[PaulOPTC]

I think I figured it out,

It seems to be working, Im sure more experienced VBA people would be able to write it better but this is what I have in case anyone comes across this in the future:

Sub QBCopy()

Sheets("Profit and Loss by Customer").Select
Cells(5, 1).EntireRow.Copy
    Sheets("QB Link").Select
    Cells(1, 1).PasteSpecial xlPasteValues

Sheets("Profit and Loss by Customer").Select
Cells(1, 1).Select
    Set Rng = Range("A1", "AAA" & Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row)
    For i = Rng.Cells.Count To 1 Step -1
        If Rng(i).Value = "Total Expenses" Then Range(Rng(i).EntireRow, Rng(i).EntireRow).Copy
    Next i
    Sheets("QB Link").Select
    Cells(2, 1).PasteSpecial xlPasteValues
    
    Sheets("Profit and Loss by Customer").Select
    Set Rng = Range("A1", "AAA" & Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row)
    For i = Rng.Cells.Count To 1 Step -1
        If Rng(i).Value = "Total Other Expenses" Then Range(Rng(i).EntireRow, Rng(i).EntireRow).Copy
    Next i
    Sheets("QB Link").Select
    Cells(3, 1).PasteSpecial xlPasteValues
    
        Sheets("Profit and Loss by Customer").Select
    Set Rng = Range("A1", "AAA" & Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row)
    For i = Rng.Cells.Count To 1 Step -1
        If Rng(i).Value = "Total Payroll" Then Range(Rng(i).EntireRow, Rng(i).EntireRow).Copy
    Next i
    Sheets("QB Link").Select
    Cells(4, 1).PasteSpecial xlPasteValues

  
End Sub

And then I did

=TRANSPOSE(1:4) in Cell A11

And then on the Table I did

Job Number	QB Job Name	Expenses	Other Expenses	Total Labor	Total Expenses
=IFERROR(VALUE(CLEAN((LEFT([@[QB Job Name]],5)))),"")	=IF('QB Link'!A12>0,'QB Link'!A12,"")	=IF('QB Link'!A12>0,'QB Link'!B12,"")	=IF('QB Link'!A12>0,'QB Link'!C12,"")	=IF('QB Link'!A12>0,'QB Link'!D12,"")	=IFERROR([@Expenses]+[@[Other Expenses]],"")

That seems to be doing what I would like it too.