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

 

[Jonmo1]

You can prevent that by calling Application.CutCopyMode = False

That will only clear the little 'Marching Ants' the appear around the range that was copied.
It will not change the fact that the range you pasted to got selected.

 

[Fable79]

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?

By luck, I obtained the result through the following code:

 Selection.Copy Destination:=Workbooks([I]yourWorkbook[/I].Name).Sheets("[I]nameSheet[/I]").Range("A1")

Ciao
Fabiana

 

[Jrgsua83]

Hello. I´m Trying to copy some rows from a sheet and paste them under the active cell but transposed. I´m using this single code

Sub Mis_Datos()
Range("J9:M12").Copy
Range(Cells(ActiveCell.Row + 1, ActiveCell.Column)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True




End Sub


But I get the following error:

Error en el método 'Range' de objeto '_Global'

Any help please??