Macro to copy and paste range form one sheet to next available row in 2nd sheet

**bajdr47** · 10-21-2012, 05:32 PM

Evening All,

Could i have assistance in getting some VBA, to copy a specified cell range in a worksheet to a list of data in another sheet.

i have to build a data entry form, but keep it as simple as possible, i have found a macro on the forum (some time ago) but it
is very complex code and i need to have a lot of control (validation) in what is entered into each cell

Thank you in Advance

Baj

**HaHoBe** · 10-21-2012, 11:47 PM

Hi, Baj,

please find attached an example of how to copy the values of a range to another sheet. The range is A5:F18, giving 14 rows and 6 columns. This shall be copied to another sheet directly under the last visible and filled cell in Column A. Please change the names to fit. If your range is different from my example please adjust the range and the number of rows and columns in the resize-command acoordingly.

Sub Baj()
Dim wsOr As Worksheet
Dim wsTa As Worksheet

Set wsOr = Sheets("Sheet1")
Set wsTa = Sheets("Sheet2")

With wsTa
  .Cells(.Cells(Rows.Count, "A").End(xlUp).Row + 1, "A").Resize(14, 6).Value = _
      wsOr.Range("A5:F18").Value
End With

Set wsTa = Nothing
Set wsOr = Nothing
End Sub

Ciao,
Holger