VBA - copy row of data and paste in new row each time

[harryhaase]

HI, I am just learning about VBA programming and have the following problem.
I need to copy data and paste it in a new row each time. The new row has about 20 columns worth of information, some of the columns are formulas that calculate the data being pasted. How do I write a vba code to paste the new data in the correct new row and the spaces that are for data and and not overwrite any preexisting formulas? IE, the data has to be placed in rows A,B,C...the, H,I,J..N,O..etc. ?

Thanks

 

[Luke M]

To find the last row of used data in a column, you can do something like this:

Dim lastRow as Long
With ActiveSheet
    lastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
End With
'To place data in next blank cell
Cells(lastRow+1,"A") = "New info"

The lastRow line says to start at bottom of col A and go up till you find something, then return the row number. This is often very useful to know.

As for skipping the columns with formulas, I'm assuming that the formulas in source range are different than destination range? Sadly, you would need to write the code to copy over each continuous section piece by piece. This is why it's helpful to keep all your data together, and the formulas either in a separate worksheet (my preferred method) or in columns at the end of the data.

 

[Smallman]

Hi Harry

To add to Luke's answer I agree you are going to need to do this one col at a time. You also should take heed to keep your calcs seperate from your inputs as this is good advice and avoids your current problem. Think of this scenario, you have data sequential in Sheet1 and want it pasted in the ragged method you mention. Something like the following should get you started.

Sub CopyMe()
Dim ar As Variant
Dim i As Integer
Dim lw As Long

lw = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
ar = [{"A", "B", "C", "H", "I", "J", "N", "O"}]

    For i = 1 To UBound(ar)
        Sheet1.Cells(Rows.Count, i).End(xlUp).Copy Sheet2.Range(ar(i) & lw)
    Next i

End Sub

Will upload a supporting file shortly.

Take care

Smallman