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.
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?
- Okay so... Manually i would do this:
For Column E in the Summarysheet: Look for the last data in column A before "Total 11111121 NAME"
For Column F in the Summarysheet: Look for the last data in column J
For Column G in the Summarysheet: Look for the last data in column Q
But the issue is that the amount of Rows with Data starting from Row 8 is variating. Otherwise i would just link it with the cell as in your first code.
I am sorry for my bad communication. I try my best to explain the issue. Thank you very much.
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.