Copy and Pasting dynamic data into blank rows and of another sheet

I am using the below code to import certain (dynamic)rows from a emailed sheet to a sheet on the next blank row
however when I am importing the date and time it is only pasting on the first row but not on subsequent rows if the initial import is multiple rows.
So I am trying to add the date and time and include the sheet name of the original sheet on all the rows of the import.

Sub copyMacroOSD()

    Dim strName As String
    Selection.Copy
    Workbooks.Open fileName:="Z:\Test.xlsx"
    On Error Resume Next
    strName = InputBox(Prompt:=" Is this a Shortage or an Overage?. Type 1 for Short and 2 for Over", _
          Title:="ENTER the Choice", Default:="none")
        If strName = "Enter Choice here" Or _
           strName = vbNullString Then
           Exit Sub
        Else
          Select Case strName
            Case "1"
                Sheets("Short").Select
                Sheets("Short").Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial _
                        Paste:=xlPasteAll
                            Application.CutCopyMode = False
                Sheets("Short").Range("A65536").End(xlUp).Offset(1, 0).Value = Date & " " & time
            Case "2"
                Sheets("Over").Select
                Sheets("Over").Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial _
                     Paste:=xlPasteAll
                            Application.CutCopyMode = False
                Sheets("Over").Range("A65536").End(xlUp).Offset(1, 0) = Date & " " & time
          End Select
        End If
'ActiveWorkbook.Close SaveChanges:=True

End Sub

I do not see the copy method in your code, only paste command

Sub copyMacroOSD()

    Dim strName As String
    Selection.Copy
    Workbooks.Open Filename:="Z:\Test.xlsx"
    On Error Resume Next
    strName = InputBox(Prompt:=" Is this a Shortage or an Overage?. Type 1 for Short and 2 for Over", _
          Title:="ENTER the Choice", Default:="none")
        If strName = "Enter Choice here" Or _
           strName = vbNullString Then
           Exit Sub
        Else
          Select Case strName
            Case "1"
              
                Sheets("Short").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
                        Paste:=xlPasteAll
                       
                Sheets("Short").Range("A" & Rows.Count).End(xlUp).Offset(1) = Date & " " & Time
            Case "2"
                Sheets("Short").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
                     Paste:=xlPasteAll
                        
              Sheets("Short").Range("A" & Rows.Count).End(xlUp).Offset(1) = Date & " " & Time
          End Select
        End If
'ActiveWorkbook.Close SaveChanges:=True
     Application.CutCopyMode = False
End Sub

Selection.Copy

is what I am using..Since the sheets are emailed to a secondary mailbox , they are opened temporarily ,and the email saved on the server.
is there a better way??

Hmm!
you are copying and then open and paste the selection, but where is the source data sheet on the code. Or are you copying from the active sheet?

yes I am copying from the Active sheet.

A long way to go:
Separate the source book and destination book.
Source Book: You should have this workbook, then sheet name, then range
Put this at the top of the code

Set wb = ThisWorkbook
Set shtMy = Sheets("sheet1")

Then select range.
Let's say

shyMy.range("A1:Z")

done!

but I am running the code from my personal macro sheet so will it apply to any workbook opened.

sorry when I said Done! I meant I in-putted the code but its not doing anything..
any help with the post ?

We then need to link the wbk with strName and input box

Now, we need to set up the destination workbook

Set wbk = Workbooks.Open(Filename:="Z:\Test.xlsx")

What is the name of the sheet from the Z/Test?

there are two sheets based on the inputbox sheet1("over") and sheet2("short")

 For Each ws In wbk.Worksheets(Array("over", "short"))
        With ws

'do something

ok after this next?http://www.excelforum.com/newreply.p...5824&noquote=1