Trying to copy fixed row of cells, paste to next empty line, and clear fixed line.

B29 Macro Question.xlsx

Hi great contributors -

I'm having a terrible time trying to create a macro to do the following:

- Copy a fixed row of cells (B29:AD29)
- paste it to the next empty row (starting at C30:AD30 and on into infinity)
- clear the value of the fixed row cells B29:AD29

I have a macro button in B29, dropdown data validation box in c29, hidden column in column d, hyperlinked vlookup reference in e29, text formatted data cell in f29, macro buttons in g29 and h29, nothing in i29, and macro buttons in j29 and k29. then L29 to AD29 are number format data cells. column A is an index number for each data entry line.

basically, i had all these above buttons, dropdowns, and hyperlinks in 500 rows from B29:AD29 to B529:AD529. That made my file size huge and processing slow. Instead, i'm trying to:
1) make cells B29:AD29 my data entry/collection cells, and
2) make cells B30:AD30 to B530:AD530 as my data recording cells.

any help in creating this macro is appreciated.

Thank you very much.

So, if i wanted to include that in part of my macro where I record the time the item was completed, would I do the following:

Sub Completed()
Dim cbx As Button
'Application.Caller returns the name of the CheckBox that called this macro
Set cbx = ActiveSheet.Buttons(Application.Caller)
With cbx.TopLeftCell.Offset(0, 7)
.Value = Now()
End With
With cbx.TopLeftCell.Offset(0, 0)
.Value = Now()
If Not IsEmpty(cbx.TopLeftCell.Offset(0, 6)) Then cbx.TopLeftCell.Offset(0, 8) = ((cbx.TopLeftCell.Offset(0, 7) - cbx.TopLeftCell.Offset(0, 6)) * 86400 / 60)
End With

WITH
Range("B29:AD29").Copy
Range("B20:AD20").Select
Selection.Insert Shift:=xlDown
End with

End Sub

thanks mehmetcik! you're really getting me there! I've got a couple of follow ups:

1) the "Selection.Insert Shift:=xlDown" always inserts into B20:AD20. I'd like it to paste to b20:AD20, then B21:AD21, then B22:AD22, and so on. how would that work?

2) also, the "Range("B29:AD29").Copy" line copies the macro buttons, dropdown list, hyperlink, and data to the row that it pastes to. I'd like to only copy the data (whether it's text, number, general, or custom formats - which i use all four of in my line b29:AD29). How can I copy just the data without the macros, dropdown list, and hyperlinks?

Thanks, again, so much!

thanks for your continued help mehmetcik.

the last code you posted [Range("B" & Cells(rows.count,2).End(xlUp).Row & ":AD" & Cells(rows.count,2).End(xlUp).Row).value = Range("B29:AD29").value] actually posts to the line above b29. I've merged the two codes as follows:

Range("B30:AD30").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Range("B30:AD30").Value = Range("B29:AD29").Value'
Range("B" & Cells(Rows.Count, 2).End(xlUp).Row & ":AD" & Cells(Rows.Count, 2).End(xlUp).Row).Value = Range("B29:AD29").Value

*note the third line is rendered inactive.

have i merged them incorrectly, or am i missing something else?

also, i'd like to copy c29, and paste it as value only to the next empty row. it's a data validation drop down box, and i'd like to avoid having hundreds of dropdown boxes copied to the rows below. if need be, the range b29:k29 could be copied and pasted AS VALUES, and the remaining L29:AD29 could be copied and pasted normally (this is important as L29:AD29 includes both cells with numbers and cells with forumulas).

Thanks again.