change in a VBA code to enable the the range

[hanim554]

Dear friends,

I have below code working perfect to extract data to many sheets filtering by coloumn E, it is extracting starting fromB2, i want it start from B5, i am unable to trigger where to change the code any help please....

Sub extract
Dim sh As Worksheet, sh1 As Worksheet
Dim r As Range, cell As Range, r1 As Range
Set sh = Worksheets("Paste Segment")
Set r = sh.Range("E5:E3000")
For Each cell In r
 If Len(Trim(cell.Value)) > 0 Then
  Set sh1 = Nothing
  On Error Resume Next
  Set sh1 = Worksheets(cell.Text)
  On Error GoTo 0
  If Not sh1 Is Nothing Then
  Set r1 = sh.Range(sh.Cells(cell.Row, "B"), sh.Cells(cell.Row, "Q"))
  r1.Copy sh1.Cells(sh1.Rows.Count, "B").End(xlUp).Offset(1, 0)
 
  End If
 End If
Next
 
End sub

Regards
Hanim

 

[dan_l]

I hate to ask, but can you post a sample? this might be easier if I can see it....

 

[hanim554]

I hate to ask, but can you post a sample? this might be easier if I can see it....

Hi,

Thanks for your time.
in the below sample data in rangeB4:F10 sheet Paste segment, and unique code in column E, based on that above code extract to sheet DLB and DB. in th extracted sheet data start from B2, i want this start fromB5. hope i conveyed my question.

Name regno SL code date
aaaaa 23433 A DLB 14/03/2013
bbbb 34354 D DLB 15/03/2013
ccccc 23223 F DB 14/03/2014
dddd 23233 E DLB 15/03/2014
eeee 23223 S DB 14/03/2015
ffffff 23444 C DLB 15/03/2015

 

[SirJB7]

Hi, hanim554!
Without a sample file (as asked by dan_l, not as a few lines posted) I can only guess to change this:
Offset(1, 0)
by this:
Offset(4, 0)
Regards!

 

[hanim554]

Hi Sir,

Thank you it is working now.

Thank you.
Hanim

 

[SirJB7]

Hi, hanim554!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!