1004 Error Copying and Pasting Range

[stinkingcedar]

Hi everyone,

I am attempting to construct a code that will cycle through all sheets in a workbook (except for the first two), finding a range in each, copying that range and then pasting it to a specific workbook based on a case. I keep getting an error message.

Public Sub newcopy()

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

region = Range("D1").Text

WSCount = Worksheets.Count-2
Application.ScreenUpdating = False

For i = 3 to WSCount + 2[INDENT]Set sht = Sheets(i)
Sheets(i).UsedRange
StartCellRow = sht.Cells.Find("keyphrase", LookAt:=Whole,_
LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection_
:=xlNext).Row + 1
sht.Range(Cells(StartCellRow, 6), Cells(StartCellRow + 29,_
27)).Copy

Select Case region
[/INDENT]
[INDENT=2]Case Is = "A"
Sheets("A").Cells((30*(i-1))+4,6).PasteSpecial (xlPasteValues)

Case Else
Sheets("I").Cells((30*(i-1))+4,6).PasteSpecial (xlPasteValues)

[/INDENT]
[INDENT]End Select[/INDENT]
Next i

End Sub

The specific line that keeps getting the 1004 error message is this one:

sht.Range(Cells(StartCellRow, 6), Cells(StartCellRow +29,27)).Copy

 

[MARK858]

Just a guess as I haven't read through the rest of the code but do you get an error if you change that line to

sht.Range([COLOR="#FF0000"]sht.[/COLOR]Cells(StartCellRow, 6), [COLOR="#FF0000"]sht.[/COLOR]Cells(StartCellRow +29,27)).Copy

 

[stinkingcedar]

That worked perfectly. Thank you!