Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help needed with Range Copy and Paste

  Asked By: Trupti    Date: Sep 09    Category: MS Office    Views: 1245
  

I am a beginner in VBA and I am trying to obtain the script for a
task which, however simple it might look, I just cannot seem to get
it to work.
Concretely, I am trying to copy a range of cells in Worksheet1. The
uppermost left cell is B1 and the lowermost right cell is on column
D and the row is the lowest row of the used range.
Further, I want to paste this selection of 3 columns and n rows to
the bottom of the data that I already have in Worksheet2 (in my
trial columns A to C and again a number of rows which can vary)(I
want to keep the data existent in Worksheet2 and paste to its bottom
the data from Worksheet one)

I tried to do something like this:

Sub CopyAndPaste()

Dim x As Integer
Dim y As Integer
Dim z As Integer

x = Worksheets(1).UsedRange.Rows.Count
y = Worksheets(2).UsedRange.Rows.Count + 1
z = x + y

Sheets("Sheet1").Select
Range("B1:Dx").Select
Selection.Copy

Sheets("Sheet2").Select
Range("Ay:Cz").Select
ActiveSheet.Paste


End Sub

but I have no idea why it doesn't work, obviously there is a problem
with the language I used.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Rae Fischer     Answered On: Sep 09

Change ur range  to the below
Sub CopyAndPaste()



Dim x As Integer
Dim y As Integer
Dim z As Integer

x = Worksheets(1).UsedRange.Rows.Count
y = Worksheets(2).UsedRange.Rows.Count + 1
z = x + y

Sheets("Sheet1").Select
Range("B1", "D" & x).Select //Changed
Selection.Copy

Sheets("Sheet2").Select
Range("A" & y, "C" & x + 1).Select //Changed
ActiveSheet.Paste
End Sub

Note: altered ur Range("A" & y, "C" & x + 1).Select since the destined
range exceeds the source range

 
Didn't find what you were looking for? Find more on Help needed with Range Copy and Paste Or get search suggestion and latest updates.




Tagged: