Macro to paste data into the next blank cell

[scott-atkinson]

Guys,

I wonder if you can help me.

I am trying to record a Macro that will allow me to copy cells from one worksheet, and then paste them as values into the next available blank cell in another worksheet.

I am completely stumped, any ideas?

 

[chergh]

This should give you somewhere to get started.

Sub bleh()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim i As Long


Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("sheet1")
Set ws2 = wb.Worksheets("sheet2")

lastrow = ws1.Range("A1").End(xlDown).Row

For i = 1 To lastrow

ws2.Range("A1").End(xlDown).Offset(1, 0) = ws1.Range("A" & i)

Next i


End Sub

 

[scott-atkinson]

This should give you somewhere to get started.

Sub bleh()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim i As Long


Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("sheet1")
Set ws2 = wb.Worksheets("sheet2")

lastrow = ws1.Range("A1").End(xlDown).Row

For i = 1 To lastrow

ws2.Range("A1").End(xlDown).Offset(1, 0) = ws1.Range("A" & i)

Next i


End Sub

Could you tell me how this works, so I can adapt it to my requirements?

 

[chergh]

set wb etc i'm hoping don't need explanation.

last row = ... determines which is the last row that contains data in in the column A1, a more correctway to do this would be:

lastrow = ws1.Range("65536").End(xlup).Row

The for i = 1 to lastrow starts a loop that will move through each cell till it reaches the last cell containing data

ws2.Range("A1").End(xlDown).Offset(1, 0) = ws1.Range("A" & i)

means that the first empty cell in colum A of sheet2 will be assigned the value that is in the current cell on sheet1.

next i moves to the next cell effectivley.

Thats a rough explanation but take the time to read the VBA help files on anything you don't understand.