-2

I am new to VBA so I am only doing record macro and editing from it. I have some data in a table that is captured from formulas and I need to 'archive' them so that when the source is changed, the data is still there. I recorded a macro to copy the formula in the row to paste it on the next row and copy and paste numbers on the same row so that the data will not changed when the source is changed. But I want the macro to do the same for the following row when I click it the next time, how can i edit my code to do so? 

Range("B20:K20").Select
Selection.Copy
Range("B21:K21").Select
ActiveSheet.Paste
Range("B20:K20").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Instead of copying from row 20, i want the macro to copy from row 21 and paste on row 22 the next time I run the macro. Please help! Thank you!

Community
  • 1
  • 1
yeocs93
  • 31
  • 1
  • 9
  • 1
    [Find the last row](http://stackoverflow.com/questions/13686801/how-to-determine-the-last-row-used-in-vba-including-blank-spaces-in-between) and use that, instead of a static range. – luke_t Feb 21 '17 at 08:58
  • Possible duplicate of [How to determine the last Row used in VBA including blank spaces in between](http://stackoverflow.com/questions/13686801/how-to-determine-the-last-row-used-in-vba-including-blank-spaces-in-between) – A.S.H Feb 21 '17 at 09:49

2 Answers2

1

you could use:

With Cells(Rows.Count, "B").End(xlUp).Resize(, 10) 
    .Copy .Offset(1)
    .Value = .Value
End With

where

  • Cells(Rows.Count, "B").End(xlUp)

    references the last not empty cell in column B

  • .Resize(, 10)

    extends the referenced cell to span 10 column (i.e. till column K)

  • .Copy .Offset(1)

    copy the referenced range to the row below

  • .Value = .Value

    leaves values only in reference range

user3598756
  • 28,893
  • 4
  • 18
  • 28
  • @yeocs93, did you get through it? – user3598756 Feb 24 '17 at 07:33
  • Hi! Yes, it worked! Thank you so much for your detailed explanation. But how can i also copy B20:K20 and paste as numbers after pasting the formulas from B20:K20 to B21:K21? – yeocs93 Mar 01 '17 at 05:57
  • You are welcome. And please explain with some examples your last question.so that I can try and help you more, BTW If my answer solved your _original_ question then you may want to mark it as accepted by clicking on the check mark beside the answer to toggle it from greyed out to filled in. Thank you – user3598756 Mar 01 '17 at 07:02
  • Thanks, i kind of figured it out myself already. Thank you for your help! – yeocs93 Mar 06 '17 at 02:01
0

You just have to find the first empty row, so you can write to the same. The below function can give you the first empty row based on cells in column B.

Function first_empty() As Long
Dim i As Long
i = 0
For Each cell In ThisWorkbook.Sheets("<sheet_name>").Columns(2).Cells 'iterates over all values in column B for sheet with name <sheet_name>
i = i + 1
If IsEmpty(cell) = True Then
first_empty = i
Exit For
End If
Next cell
End Function
Arun
  • 136
  • 1
  • 10