(Help) Moving row with the macro

[osevero]

Hi to everyone!

I need help... These are the rows of my spreadsheet:

st.jpg

So what I what is that the macro add the last row (just like the existing ones) moving down, keeping the formatting (and functions) but with clean cells.
Then in another macro, I would like to save data from all existing rows to another spreadsheet.

I appreciate a lot some help!

Thanks!

[patel]

attach please a sample file (xlsx) with dummy data, not empty

[osevero]

program2.xlsx

Done

Help please!

[osevero]

what I need is that the macro add the last row (just like the existing ones) moving down, keeping the formatting (and functions) but with clean cells.
Then in another macro, I would like to save data from all existing rows to another spreadsheet.

[patel]

Sub add1()
With Sheets("Production")
  LR = .Cells(.Rows.Count, "C").End(xlUp).Row
  .Rows(LR + 1).Insert
  .Range("B" & LR & ":M" & LR + 1).FillDown
  .Range("B" & LR + 1 & ":M" & LR + 1).SpecialCells(2).ClearContents

End With
End Sub

[osevero]

Thanks for the reply!
Unfortunately this is not possible due to the fact that when we create a combo box we will need to select a cell link, so it's complicated to program a macro to always select different links cell. Then I decided that there are 9 rows to fill with data:
program3.xlsx

And now I would like to save the data from 9 rows to another spreadsheet, but I would just like to save the rows with data... anyone knows?

Another problem, I can't copy the data selected from the combo box paste into another spreadsheet...

Can anyone help me?

[david000]

On the Data sheet consider using a dynamic Data Validation list. This way you can copy the values straight from the sheet. Unless you are using the index of those combo boxes for something you didn't specify here.

Named Range:
=OFFSET(Data!$A$2,1,0,COUNTA(Data!$A:$A)-1,1)
Data Validation > List > Source = Named Range

This just copies the whole table over to the next sheet.

Sub copy_resize_tester()
Plan3.Range("b3").Resize(Cells(Rows.Count, "b").End(xlUp).Row - 2, Cells(3, Columns.Count).End(xlToLeft).Column - 1).Copy
Sheet1.Range("a3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

[osevero]

That helped me a lot, thank you!!