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?

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

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?

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.