Pasting a range

F

Francis Hookham

I need to paste the range "WHrange" from sheet "WidthHeight" to R1C1 in
sheet "Results" BUT I only want Values and Formats.

Incidentally, what already exists in the Results" sheet has to be moved to
the right to make space so seven columns must be inserted first - I can do
that by selecting the first seven columns and "Insert Column" but can that
be done indirectly too?

Ok I can record but I should prefer to do it directly, rather than messy
time wasting switching from one sheet to the other. I have tried but cannot
get it right.

Thanks.

Francis Hookham
 
D

Dave Peterson

One way:

Option Explicit
Sub Testme()
Dim RngToCopy As Range
Dim DestCell As Range

Set RngToCopy = Worksheets("WidthHeight").Range("WHRange")

Set DestCell = Worksheets("Results").Range("a1")
DestCell.Resize(1, RngToCopy.Columns.Count).EntireColumn.Insert
Set DestCell = DestCell.Offset(0, -RngToCopy.Columns.Count)

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues
DestCell.PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

End Sub

This actually moves all of column A to the right--not just the rows that would
be overwritten by WHRange.
 
F

Francis Hookham

Dave Peterson has given me one way which I have not yet tried. It is always
good, especially for an amateur XL user like me (20 years!), to learn so do
please comment if you can spare the time - thank you.

Sub TransferResults()
Application.ScreenUpdating = False
Sheets("Results").Select
Columns("A:G").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Application.Goto Reference:="WHresults"
Selection.Copy
Sheets("Results").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B4").Select
Application.CutCopyMode = False
End Sub
 
Top