CODEWITHSUNDEEP
vbaexcelpaste
Johnny Dubbaneh
Johnny Dubbaneh

Reputation: 33

Copy & Transpose Values from 1 Workbook to Another

Sub LoopThroughDecTab()
Dim MyFile As String
Dim erow
Dim FilePath As String

FilePath = "C:"
MyFile = Dir(FilePath)


Do While Len(MyFile) > 0
If MyFile = "Dec Tab Macro.xlsm" Then
Exit Sub
End If

Workbooks.Open (FilePath & MyFile)
ActiveWorkbook.Worksheets("Declaration Analysis (Source)").Activate
Range("H9:H21").Copy
ActiveWorkbook.Close False

'Getting Runtime error PasteSpecialMethod of Range Failed on following line'

ActiveSheet.Range(Cells(erow, 1), Cells(erow, 7)).PasteSpecial.Range Transpose:=True 

MyFile = Dir

Loop

End Sub

I have files in a folder, the code loops through the files copies values and then I want those values Transposed into the Active MasterSheet. There are 7 values that need to be pasted, and then it should open the next workbook in folder and repeat the process.

Upvotes: 1

Views: 1603

Answers (3)

FreeMan
FreeMan

Reputation: 5687

Assuming that you posted your complete code, and simply interjected the 'non-code' message to tell us where your error was, give this a try:

Option Explicit

Sub LoopThroughDecTab()
Dim MyFile As String
Dim erow
Dim FilePath As String
Dim DestWB as Workbook
Dim SourceWB as Workbook

'this way we know the one where the code is running and the destination for our copies
set DestWB = ThisWorkbook 

FilePath = "C:"
MyFile = Dir(FilePath)

Do While Len(MyFile) > 0
    If MyFile = "Dec Tab Macro.xlsm" Then
        Exit Sub
    End If

    Set SourceWB = Workbooks.Open (FilePath & MyFile)
    SourceWB.Worksheets("Declaration Analysis (Source)").Range("H9:H21").Copy
'Move the close to AFTER you do the paste
'NOTE: You may have to make a change here:
    DestWB.Range(Cells(erow, 1), Cells(erow, 7)).PasteSpecial.Range Transpose:=True 
    SourceWB.Close False
    MyFile = Dir
Loop

End Sub

If you open two workbooks (A & B) in Excel, copy some cells from A, close A, then try to paste into B, you'll have nothing left to paste - closing A clears the clipboard buffer. I believe the same thing is happening here.

Important Notes:

  1. I've removed all references to ActiveWorkbook - I believe that I've gotten it correct, but I always get a bit lost when using Active*, so I avoid it at all cost. (There are a very few situations where it's the only way to get things done, this isn't one of them.) If I've messed up your source & destinations for the copy, simply reverse the Set statements so you're using them the other way.
  2. I'm not sure where erow and FilePath are getting set, so I'm assuming this wasn't the complete code. The assumption is that they'll still get set somehow.
  3. I've not used the copy/transpose function, so you may well need to include Excel Developers's adjustments, as well.

Upvotes: 1

Excel Developers
Excel Developers

Reputation: 2825

set CopyFromRange = Range("H9:H21")
set CopyToRange = ActiveSheet.Cells(erow,1).Resize(1,13)
CopyToRange.Value = Application.Transpose(CopyFromRange.Value)

Upvotes: 0

genespos
genespos

Reputation: 3311

It's difficult to understand what's the problem without seeing what are you copying, but you can try: 

ActiveSheet.Cells(erow, 1).PasteSpecial Transpose:=True

Upvotes: 0

Related Questions