1004 Error

[LordTim]

I keep getting an error after copying several cells and pasting them as values with transpose. I have tried adjusting my code in a couple of different ways to stop it but I must be missing something because this is a simple copy and paste.

Set myBWb = ThisWorkbook
RecCount = 1
ImportFile = Dir(myStrPath & "\*.xpf")
Do While ImportFile <> ""
Set myAWb = Workbooks.OpenXML(myStrPath & "" & ImportFile)
myAWb.Sheets(1).UsedRange.Copy myBWb.Sheets(1).Cells(RecCount, 1)
myAWb.Close False

' Sheets("Process").Select

LastRow = Worksheets("Process").Range("s1")
Sheets("Import").Select
Range("K3:K19").Select
Application.CutCopyMode = False
Selection.Copy

'LastRow = Cells(LRow, "A").End(xlUp).Row


Worksheets("Process").Select
ActiveSheet.Cells(LastRow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Range("T1:AJ1").Copy
Range("T2").Select

ActiveSheet.Cells(LastRow, 20).Select
Selection.Paste

The blue section is where it consistently fails. I have changed the code multiple times trying to determine the issue.

Thanks in advance for reviewing/responding.

 

[Yongle]

Is this what you are trying to achieve - note that NOTHING is selected

    Sheets("Import").Range("K3:K19").Copy
    Sheets("Process").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

 

[LordTim]

I have tested the changes above and like some of my coding it works fine until it gets to this function:

Sheets("Process").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Any coding to select another cell or perform another cell function causes an error. For example, if I place the below code immediately following this function it causes an error.

Range("A1").Select
And depending on the code such as above will change the error number. I have had at least 4 different error codes so far.

 

[Yongle]

What I provided was for you to test standalone
Does it copy & paste as you require ?

 

[Yongle]

Here are a few tips for you

1. Do not select objects - it is not required (most of the time! but there are a few exceptions)
2. Hopping backwards and forwards selecting objects makes coding tricky (is the correct sheet active ? etc) and can be unpredictable when working with multiple workbooks
3. It makes the code run MUCH slower
4. Instead of selecting, use variables to refer to objects
5. Insert Option Explicit at the TOP of your code - VBA then helps you
6. Option Explicit prevents variables that have not been declared from being used ( helps with typos too!)
7. Declaring variable type helps identify when the wrong type of value is attributed to it later in the code (the code fails at that point)

This may help you work things out.
I am a bit confused by what you are trying to do towards the end of your code
Instead of using Cells(1 , 20 ) you can use the column letter Cells(1 , "T" ) - both are correct, but the latter is more obvious until more familiar

Option Explicit    ' MUST place at TOP of module above everything else

Sub copyPaste()
[COLOR=#006400]'declare variables that will be useful[/COLOR]
    Dim myBwb As Workbook, wsPro As Worksheet, wsImp As Worksheet
    Dim lastRow As Long
[COLOR=#006400]'set variables for workbook, sheets[/COLOR]
    Set myBwb = ThisWorkbook
    Set wsPro = myBwb.Sheets("Process")
    Set wsImp = myBwb.Sheets("Import")
[COLOR=#006400]'copy[/COLOR]
    wsImp.Range("K3:K19").Copy
[COLOR=#006400]'paste transposed to row below last row with value in column A[/COLOR]
    wsPro.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
[COLOR=#006400]'determine last row ???[/COLOR]
    lastRow = wsPro.Cells(Rows.Count, "T").End(xlUp).Row    'replace this with however you want to calculate last tow
      
[COLOR=#006400]'copy and then paste to row below lastRow   (I am unsure which seet to copy from and which sheet is the destination  - amend to suit)[/COLOR]
    wsPro.Range("T1:AJ1").Copy Destination:=wsPro.Cells(lastRow, "T").Offset(1)

[COLOR=#006400]'activate a sheet and then select a cell like this[/COLOR]
    With wsPro
        .Activate
        .Range("A1").Select
    End With

End Sub