VBA copy match text one row above data from one sheet to summay sheet

SAMHOTONY

New Member
Joined
Nov 5, 2017
Messages
4
My workbook has summary sheet and many different sheets ( sheet name 1809.1810,1811 son one). each sheet has text `subtotal on column D , but different sheet row for subtotal is different. I want to copy data from one row above from subtotal row to column P to summary sheet one by one for sheet. example. copy range should be (D5:P & one row above subtotal`) . my VBA below copy all data from different sheet, dont know to how to code for this , please help me.
Code:
Public Sub CopyToSummary()
Dim ws  As Worksheet, _
    LR1 As Long, _
    LR2 As Long
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets


If ws.Name <> "Summary" And (Left(ws.Name, 1) = "1" Or Left(ws.Name, 1) = "4" Or Left(ws.Name, 1) = "6" Or Left(ws.Name, 1) = "7") Then


           
        LR1 = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row + 1
        
        'need help to copy one row above "subtotal"
        LR2 = ws.Range("D" & Rows.Count).End(xlUp).Row  ' coping all data from sheet.
        
        ' copy from sheets to summary sheet
        
        ws.Range("D5:p" & LR2).Copy Destination:=Sheets("Summary").Range("A" & LR1)
    End If
Next ws
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Try making this mod
Code:
ws.Range("D5:p" & LR2[COLOR=#ff0000]-1[/COLOR]).Copy Destination:=Sheets("Summary").Range("A" & LR1)
 
Upvote 0
Need some help. want to change code below , dont know how to code to select one row above to text subtotal [ range. D4: column P row (one row above text ``subtotal)] `i have vba code as follows :
Code:
Public Sub CopyToSummaryTest()
Dim ws  As Worksheet
   Dim LR1 As Long
    Dim LR2 As Long
    Dim MyRng As Long
Application.ScreenUpdating = False


 
 For Each ws In ActiveWorkbook.Worksheets


        If ws.Name <> "Summary" And (Left(ws.Name, 1) = "1" Or Left(ws.Name, 1) = "4" Or Left(ws.Name, 1) = "6" Or Left(ws.Name, 1) = "7") Then
    
             For Each cell In ws.UsedRange.Cells


                 If LCase(cell.Value) = "subtotal" Then
                
                'want to change code below ,  dont know how to code to select one row above to text subtotal [ range. D4: column P row (one row above subtotla)]
                LR2 = Sheets(ws.Name).Range("d" & Rows.Count).End(xlUp).Row ' how to set row above subtotal text
                
                       ' cell.Value = cell.Value.Offset(-1, 0).Value
                        LR1 = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row + 1
                        
                        ws.Range("D5:p" & LR2).Copy Destination:=Sheets("Summary").Range("A" & LR1)
                 End If
        
             Next
        
        End If
    Next ws
    
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Have you tried my suggestion in post#2?
 
Upvote 0
Have you tried my suggestion in post#2?

Hi Fluff, I need to select range from one row above where match Text `subtotal`. your post will go one low above from last row of data what i don`t want. sheets having more row after sub total what do need to copy to summary sheet , only need row above from subtotal. Could you please have a look my VBA code and advise what code i need to select rang row above text `subtotal`.
 
Upvote 0

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

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
Back
Top