Copying Additional Data into Separate Sheets

[stinkingcedar]

Hey guys,

First things first here is my code:

Option Explicit


Public Sub CopyDataLoad()


Dim WSCount As Long, StartCellRow As Long, i As Long
Dim sht As Worksheet
Dim region As String






WSCount = Worksheets.Count
Application.ScreenUpdating = False


For i = 3 To WSCount
    
    region = Sheets(i).Range("D1").Text
    Set sht = Sheets(i)
    Sheets(i).UsedRange
    StartCellRow = sht.Cells.Find("Please DO NOT TOUCH formula driven:", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
    sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy
    
    
    Select Case region


        Case Is = "A"
        Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
        Case Else
        Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
    
    End Select


Next i


End Sub

So just to give you a quick background...

I am attempting to write a code that cycles through every sheet of a workbook(except the first two) copies two specific ranges from each sheet, and then pastes that into the appropriate sheet (1 or 2).

I am able to get all the larger ranges with the data to copy and paste over(which is all I am doing with the code above), but I can not seem to figure out how to get the text from cell E5 on every sheet to copy over to the new sheet. This cell's text needs to be copied over in the same row as the starting point for the larger range, but offset 5 columns to the left to column A.

I know this is a rather confusing question, so if any clarifications are needed in order to answer please don't hesitate to ask.

Thank you!