VBA: PasteSpecial Using Destination Command

[brian_griffin]

Hello everyone,

I using a VBA code to copy cells from various excel sheets into one master sheet.

I have managed to employ VBA to copy single cells, but now I would like to copy a range of cells and transpose them into the master sheet.

However, I am not able to adapt the paste special command with the destination command.

My recent code is:

Dim WBQ As Workbook
Dim WBZ As Workbook
Dim lngAnzahl As Long
Dim lngLastQ As Long
 Set WBZ = ActiveWorkbook

...

Set WBQ = Workbooks.Open(Filename:=varDateien(lngAnzahl))
  lngLastQ = WBQ.Worksheets(1).Range("A65536").End(xlUp).Row
  WBQ.Worksheets(1).Range("H22").Copy _
  Destination:=WBZ.Worksheets(1).Range("J" & WBZ.Worksheets(1).Range("J65536").End(xlUp).Row + 1)
WBQ.Close

I would like to copy the cells H22 to H30 into the destination worksheet.

Has anyone any hint? I really appreciate any help.

Thanks in advance,
Brian

 

[Andrew Poulsom]

Welcome to MrExcel.

You need 2 lines for Copy/PasteSpecial. You can't use Copy's Destination argument.

 

[Jonmo1]

The Destination:=Range(...) only works on a straight copy/paste.
It doesn't apply to the paste special method.

You have to do it in 2 lines, one to copy, and one to paste special

Something like

    Range("A1:A3").Copy
    Range("B1:D1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

 

[brian_griffin]

wow... so fast repsonses.

Andrew & jonmo1 thank you so much!

Brian

 

[Damby]

wow... so fast repsonses.

Andrew & jonmo1 thank you so much!

Brian

Hi GuysI had the same Problem but I still have one question, how do I define he new spredsheet that I added?
With Destination there is no Need to define it..


Thanks

 

[Andrew Poulsom]

Qualify the Range with its workbook and worksheet. The code in the original post would be:

  WBQ.Worksheets(1).Range("H22").Copy
  WBZ.Worksheets(1).Range("J" & WBZ.Worksheets(1).Range("J65536").End(xlUp).Row + 1)").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

 

[rilzniak]

Qualify the Range with its workbook and worksheet. The code in the original post would be:

  WBQ.Worksheets(1).Range("H22").Copy
  WBZ.Worksheets(1).Range("J" & WBZ.Worksheets(1).Range("J65536").End(xlUp).Row + 1)").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

What about copying a worksheet from one workbook to another? I'm trying to copy/paste values on one workbook, and paste it to another workbook that is closed (it could be open, it doesn't matter to me). The problem line I'm getting is:

 Sheets(wksht).Copy Destination:=newwkbk.Sheets("Sheet1")

I'm not sure why it's not working. Any ideas?

Sub openfile()


Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim FirstRow As Integer
FirstRow = 7
Dim LastRow As Integer
LastRow = 8


For i = FirstRow To LastRow


Dim myfile As String
myfile = Cells(i, "A").Value & Cells(i, "C").Value
Dim wkbk As String
wkbk = Cells(i, "C").Value
Dim wksht As String
wksht = Cells(i, "D").Value
Set newwkbk = Workbooks.Open("C:\Users\rstoneham\Desktop\Inventory Project\2014\Jan.xlsx")
Dim FolderYr As String
FolderYr = "2014"
   
        Application.Workbooks.Open FileName:=myfile


        Worksheets(wksht).Activate
        ActiveSheet.Cells.EntireRow.Hidden = False
        ActiveSheet.Cells.EntireColumn.Hidden = False
        Range("A1:CZ250").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues


        Sheets(wksht).Copy Destination:=newwkbk.Sheets("Sheet1")


        newwkbk.Activate
        Worksheets(wksht).Activate
    
    On Error Resume Next
    
        Cells.Find(What:="Production Facility", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
            
    If Err.Number <> 0 Then
    
    Range("A5").Activate
    ActiveSheet.Name = Application.Proper(ActiveCell.Value)
            With ActiveSheet.Tab
                .ColorIndex = xlNone
                .TintAndShade = 0
            End With
    
    End If
    
        ActiveCell.Offset(rowOffset:=0, ColumnOffset:=1).Activate
        ActiveSheet.Name = Application.Proper(ActiveCell.Value)
            With ActiveSheet.Tab
                .ColorIndex = xlNone
                .TintAndShade = 0
            End With
        Cells(8, 1).Select
    
        Workbooks(wkbk).Activate
        ActiveWorkbook.Close SaveChanges:=False
    
Next i


    newwkbk.Activate
    Sheets("Sheet1").Delete
    Sheets("Sheet2").Delete
    Sheets("Sheet3").Delete
    
    On Error Resume Next
            Cells.Find(What:="Opening Inv", After:=ActiveCell, LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase _
            :=False, SearchFormat:=False).Activate
                ActiveCell.Offset(rowOffset:=-1, ColumnOffset:=0).Activate
            
    If Err.Number <> 0 Then
            Cells.Find(What:="Beginning", After:=ActiveCell, LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase _
            :=False, SearchFormat:=False).Activate
                ActiveCell.Replace What:="Beginning", Replacement:="Opening Inv", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    End If
            
    Cells.Find(What:="Calculation Worksheet", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    ActiveCell.Offset(rowOffset:=1, ColumnOffset:=0).Activate
    ActiveWorkbook.SaveAs ("C:\Users\rstoneham\Desktop\Inventory Project\" & FolderYr & "\" & Format(ActiveCell, "mmm") & ".xlsx")
    
End Sub

 

[Andrew Poulsom]

The Copy method of the Worksheet object doesn't have a Destination argument.

 

[bs0d]

The Destination:=Range(...) only works on a straight copy/paste.
It doesn't apply to the paste special method.

You have to do it in 2 lines, one to copy, and one to paste special

Something like

    Range("A1:A3").Copy
    Range("B1:D1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

I've found a notable drawback of using the 2 line method is that it will leave your cells selected.

 

[rnmisrahi]

I've found a notable drawback of using the 2 line method is that it will leave your cells selected.

You can prevent that by calling Application.CutCopyMode = False