More Data Pasted Than Expected with SELECTION.OFFSET.SELECT

   SELECTION.OFFSET(rowOffset, columnOffset).SELECT
				

   x = Selection.Columns.Count
 
      Selection.Offset(0, x).Resize(, 1).Select
				

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a workbook with two worksheets, Sheet2 (the sheet that the original data will be on), and Sheet 1 (the sheet on which data will be pasted).
2. On Sheet2, type the following information:

              A        B      C
         -------------------------
   
         1    100      200    300
         2    100      200    300
         3    100      200    300
         4    100      200    300
         5    100      200    300

3. In a Visual Basic module, type the following macro:

      Sub Test()
           ' Select the first two columns from the source sheet
           Sheets("Sheet2").Select
           Range("A1:B5").Select
           Selection.Copy
           ' Paste the data into the destination sheet
           Sheets("Sheet1").Select
           ActiveSheet.Paste
           Application.CutCopyMode = False
           ' Copy the third column from the destination sheet
           Sheets("Sheet2").Select
           Range("C1:C5").Select
          ' Paste it into the destination area
           Selection.Copy
           Sheets("Sheet1").Select
           Selection.Offset(0,2).Select
           ActiveSheet.Paste
      End Sub
   						

When you run this macro, column C is pasted to fill the selected range (in this case, the selected range is C1:D5 which is the result of offsetting the originally selected range, A1:B5, by two columns).

           A        B      C      D
      ---------------------------------
      1    100      200    300    300
      2    100      200    300    300
      3    100      200    300    300
      4    100      200    300    300
      5    100      200    300    300
					

To edit this macro so that the data is copied correctly, replace the line of code that reads:

   Selection.Offset(0,2).Select
				

    x = Selection.Columns.Count
      Selection.Offset(0, x).Resize(, 1).Select
				

When you run the macro with this change included, the copied data is pasted once into the correct paste range.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.