Dynamically insert text into next empty row with userform & copy specific cells/range into next empty row if it meets criteria

[LadyRave]

Hello Everyone,

I'm sure there's a similar thread out there that addresses this issue, but I'm having trouble applying the correct codes to work with my spreadsheet. There are two things I want to do here:

1) Dynamically insert text into next empty row with userform
2) Copy specific cells/range into next empty row if it meets criteria

Issues:

• I have a userform which allows you to enter text in specific cells. In this case, it's columns L & M, starting range is L11:M11.
• If there is already data in range L11:M11, I want the userform to populate text in the next empty row below L11:M11 which would be L12:M12 and so on and so forth.
• The data is entered into cells I formatted which looks like a table. The cell ranges that are formatted are E8:Q19. Only range L11:M11 is where text from the userform is entered. I'm not sure how to insert images here so if necessary, I can send the file.
• Before the text in the userform populates the cells, there's a message box that pops up which asks if the data is for a new project. If yes, I want a code that copies the specific formatted cells into the next blank, unformatted row below. If no, I want the userform to enter the text in range L11:M11 or whichever cell below it is empty.
• I don't have codes to execute either action.

I know this is probably confusing without looking at the file so I'll be more than happy to email it. May someone please help me? I'm having trouble finding forums that apply to my specific situation.

 

[Norie]

The first part is easy.

Let's assume you have 2 textboxes on the userform, TextBox1 and TextBox2.

Range("L" & Rows.Count).End(xlUp).Offset(1).Resize(,2).Value = Array(TextBox1.Value, TextBox2.Value

What 'specific formatted cells' should be copied for a new project?

 

[LadyRave]

Hi Norie,

I only have one textbox in the userform. On the spreadsheet, columns L & M are merged together so that's where the text from the userform will populate.

If I select a new project from the message box that pops up, I basically want to copy cells E8:Q19 and paste it to the next empty row below the first project. Does this make sense?

The first part is easy.

Let's assume you have 2 textboxes on the userform, TextBox1 and TextBox2.

Range("L" & Rows.Count).End(xlUp).Offset(1).Resize(,2).Value = Array(TextBox1.Value, TextBox2.Value

What 'specific formatted cells' should be copied for a new project?

 

[Norie]

If there's only one textbox all you would need for the first part would be this?

Range("L" & Rows.Count).End(xlUp).Offset(1).Value = TextBox1.Value

As for copying E8:Q19, wouldn't that overwrite any value written to column L?

 

[LadyRave]

Copying E8:Q19 wouldn't overwrite the value written in column L because it's basically inserting another table below the first one. Please see my comments below. Outside of what I initially posted, I actually need more help than I thought.

The "New Entry" button has the code that opens the userform for you to enter data. However, the code has the following issues:

• The message box pops up after you input text and select "add". The message box should appear before any entry is made in the userform.
• Once data is entered in the userform and information is populated in the cells (L11:M11), it doesn't find the next empty row below it to enter the new text. It just overrides whatever is in the current cell.
• If the message box works correctly and I select "yes" if I want to create a new project, I want to copy E8:Q19 and paste it in the next row below so essentially a new table is created for new information for a different project can be added.
• I also want to create another code that if a cell that has data in it already to be easily modified. For example, if L12:M12 or L16:M16 has data and I want to edit them individually, I click on a button and a userform (with the current information entered from the cell I select) to be edited so I don't have to double click on the cell itself.

Does all of this make sense?

If there's only one textbox all you would need for the first part would be this?

Range("L" & Rows.Count).End(xlUp).Offset(1).Value = TextBox1.Value

As for copying E8:Q19, wouldn't that overwrite any value written to column L?