Problem Moving Data from one Sheet to Another

Hello ExcelForum,

I am writing a macro for data transfer from one sheet to another in Excel 2003 and so far this is what I have;

Sub PMoveData()
' PMoveData Macro
' Macro to move raw data from "P" to PDat
 Sheets("P").Select
    Range("E5:S5").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C45").Select
    ActiveSheet.Paste
Sheets("P").Select
    Range("E26:S26").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C46").Select
    ActiveSheet.Paste
Sheets("P").Select
    Range("E47:S47").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C47").Select
    ActiveSheet.Paste
End Sub

But when I run that code, it stops at the first

Sheets("P").Select

and tells me that the subscript is out of range.


If I get rid of the

Sheets("P").Select

it works fine for the first selection as long as I start on the "P" sheet, but messes up the rest.

Please let me know how to properly select each sheet so I can transfer the data correctly.

Thanks!!