-1

I want to copy a range of cells i.e. from 7th row of column F to column K and paste the copied cell in another worksheets(Sheet2). The range should be copied from 7th row till last blank cell and it should get pasted in (Sheet2) from B1 to B6 every time in this same range replacing the previous value.

Kindly help me to start with it.

I have done some changes as suggested, but I want the code to loop the values in sheet(2) and paste the values in sheet 3 by replacing only in row A5 to AG5. But below code is only pasting the first row. It should loop from first row to last empty cell in sheet 2 and paste in sheet 3 in range A5 to AG5.

Sub LoopCopy()

Dim EndRow As Integer

'Find the last row with data

EndRow = ThisWorkbook.Sheets(2).Range("D7").End(xlDown).Row
'Set the range in sheet 2 to the range you want to copy in sheet 1
ThisWorkbook.Sheets(3).Range("A5", "AG5").Value = ThisWorkbook.Sheets(2).Range("F7", "AL" & EndRow).Value
End Sub
Community
  • 1
  • 1
Striker
  • 237
  • 1
  • 6
  • 21
  • 2
    Well without any code provided by you there is no way we can guess where to start. Do we show you how to open the VBE. Do we discuss the difference between SUBs and FUNCTIONs. Post what code you have and we will help with the specific problems. Otherwise ???? – Scott Craner Apr 21 '16 at 18:17
  • 1
    If all you want to know is how to find the last occupied cell then look [HERE](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) – Scott Craner Apr 21 '16 at 18:20
  • @ScottCraner I need help opening the VBE. Edit: what's the VBE? – findwindow Apr 21 '16 at 21:45
  • You are not looping you are simply copying a large range and pasting it only to one row, therefore you only get the first row of data. If you want the last row of data then change the `"F7"` to `"F" & EndRow` it will now put the values from the last row. – Scott Craner Apr 22 '16 at 15:06

1 Answers1

1

It's good practice to not use the clipboard, in case you copy something while the code is running and pastes that instead of what you intended. You can set one range value to another rather than using copy and paste. For one cell that would look like this.

ThisWorkbook.Sheets(2).Range("A1").Value = ThisWorkbook.Sheets(1).Range("A1").Value

That would effectively copy the value of A1 in sheet 1 to A1 in sheet 2 without using the clipboard. You can do the same for a range of cells as follows.

ThisWorkbook.Sheets(2).Range("A1:C3").Value = ThisWorkbook.Sheets(1).Range("A1:C3").Value

If you want to get clever and dynamically define your range you can do it with the below procedure.

Dim EndRow As Integer

'Find the last row with data
EndRow = ThisWorkbook.Sheets(1).Range("F7").End(XlDown).Row
'Set the range in sheet 2 to the range you want to copy in sheet 1
ThisWorkbook.Sheets(2).Range("B1","G" & EndRow - 6).Value = ThisWorkbook.Sheets(1).Range("F7","K" & EndRow).Value

I hope this helps!

Jason Conway
  • 116
  • 7