VBA Transpose and Move to a table

PaulOPTC

New Member
Joined
Jan 13, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon,

Mrexcel.png



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 NumberQB Job NameExpensesOther ExpensesTotal LaborTotal Expenses
=VALUE(CLEAN((LEFT([@[QB Job Name]],5))))This will be taken from Row 5From "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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

PaulOPTC

New Member
Joined
Jan 13, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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 NumberQB Job NameExpensesOther ExpensesTotal LaborTotal 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,256
Messages
5,852,924
Members
431,532
Latest member
Lroy9827

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top