Forum Discussion
Excel Makros code seems right but doesn't work as expected
Please try this...
Sub CopyDataToMasterSheet() Dim wsSummary As Worksheet Dim ws As Worksheet Dim dlr As Long Dim RngTotal As Range Dim r As Long Application.ScreenUpdating = False Set wsSummary = Worksheets("Summary") For Each ws In ThisWorkbook.Worksheets If Not ws Is wsSummary Then Set RngTotal = ws.Columns(1).Find(what:="Total", lookat:=xlPart) If Not RngTotal Is Nothing Then r = RngTotal.End(xlUp).Row If wsSummary.Range("H1").Value = "" Then dlr = 1 Else dlr = wsSummary.Cells(Rows.Count, "H").End(xlUp).Row + 1 End If ws.Range("A" & r).Copy wsSummary.Range("H" & dlr) ws.Range("J" & r).Copy wsSummary.Range("I" & dlr) ws.Range("Q" & RngTotal.Row).Copy wsSummary.Range("J" & dlr) End If End If Next ws Application.ScreenUpdating = True End SubIn the attached, you may click the button called "Copy Data" on Summary Sheet to run the code.
You're welcome! Yes, it seems possible but to discard any confusion, I suggest you to upload a sample file along with some dummy data on data sheets and expected output on Summary Sheet to let me know what exactly you are trying to achieve. Add some comments on the Summary Sheet to describe the logic behind the output if necessary.
Subodh_Tiwari_sktneer Thank you so much for your reply. I hope the file is clear to you. Let's say i'll always need 1 and 12.08.2021.
Does the following code work for you?
Sub Tabelle_zusammanfassen() Dim Summary As Worksheet Dim ws As Worksheet Dim RangeTargetTab As Range Dim i As Integer Dim dlr As Long Dim n As Variant Application.ScreenUpdating = False Set Summary = Worksheets("Summary") For Each ws In ThisWorkbook.Worksheets If Not ws Is Summary Then dlr = Summary.Cells(Rows.Count, "E").End(xlUp).Row + 1 n = Application.Match(1, ws.Columns(1), 0) If Not IsError(n) Then ws.Range("A" & n).Copy Summary.Range("E" & dlr) ws.Range("D" & n).Copy Summary.Range("F" & dlr) End If End If Next ws Application.ScreenUpdating = True End SubSubodh_Tiwari_sktneer P.S. This second file now has the same amount of rows on every sheet. Of course the original one doesn't. Otherwise the first code you sent me would work again.
Best wihes
BixenteTo discard any confusion and considering the fact that your latest sample file may not still represent your actual data, could you please let me know what logic you would apply manually to pick the data to be copied to the Master Sheet? How do you identify the last line to be copied? The line which appears before a cell in column A which contains a sub-string "Total" in it on each Sheet? And if this is correct, there is only one instance of Total in column A?
e.g. on the first data sheet, cell A12 contains text "Total 111111 NAME" so the code will identify this cell and then go above and find row#10 as the last line of your data and pick the data from A12, J12, and Q12 and copy it to Master Sheet. Is this logic correct?
Subodh_Tiwari_sktneer First of all, i want to thank you for your time. I really apriciate it a lot.
Of course the code works in the Dummy File. But it doesn't work on my file. I think in spite of the file i sent you, we still missunderstood each other. If i get that right, this code only works if i am looking for "1". But of course the Data can be different.
I took the time to rewrite the file with some Dummy Data as i have it 1:1 (but with less sheets of course). While i was preparing this file, i saw a major mistake: For the column G, i don't need the data from the last row of the table in Q but the very last line. I'm so sorry. I think you should get it with this file.
Again, thank you very much for your help.