Pasting Repeated Form Template in Excel

I'm trying to create a macro for this form that I create on a weekly basis. The form appears like so:

I have 3 columns:

Date Day Close Profit - column labels

Date column has the current 5 day week (trying to determine the best formula for this column.)

Day Close column - I enter numbers every day at 5 pm.

Day Close column - Has a set formula that isn't anchored.

The ask - Is there a way to create a macro to copy the table on a weekly basis?

Every week should appear something like this

Screen Shot 2020-09-11 at 10.06.25 AM

I don't see KM has any action directly controls Excel.
Your table contains formulas. It makes things more complicated.

In order for others to better assist you, can you give more details?

Where do you want to copy to? Create a new Sheet and paste it always at "A1"?

Or do you want to paste to a following row in the same sheet? If in the same sheet, you probably won't want to paste the first head row? Also, what is C3? Is it generated by a formula or a fixed number? Do you want to copy this value as well?

Just 2 rows below. For example if weekly gains (which is the last row) is in row 24, the next "pasting" should be in row 26, so A26 with the following week.

But, @martin you are right, column C is all formulas.

Ok. That is clearer. (You have not answered what C3 is, so I'll just ignore it.)

What you need to accomplish is:

  1. copy C5-C11 to 2 rows below. (NOTE: it requires you did no editing at all in the cells that are outside the table! You will need to copy this table to a new sheet if you have edited cells outside the table. This is because Excel oddly recognizes whichever cell you have edited, even though we have cleared or deleted the cells.)
  2. copy Weekly Gains to 9 rows below.
  3. insert dates, from Mon to Fri above "Weekly Gains". (I assume you will run this macro on each Monday, so the first date is set to the current day.)

I have accomplished the first two goals. The third goal should have been accomplished. But I'm experiencing a very odd behavior:

KM would not insert the date, but what is in the System Clipboard, although I have made the action to "insert text by pasting", and the text is the variable for "date"!!

I have tried to set the System Clipboard to the date variable and simulate "Paste" or "⌘ + V", but the result is the same!

If I disable the Yellow color group above, leaving only the inserting dates actions, it will work!

Is this a bug? If so, with Excel or with KM?

I have uploaded both the macro and the test excel file.
excel.zip (9.9 KB)

Aahh, C3 in context of my image is just a blank line between weeks. so it works like this:

Monday
Tuesday
Wednesday
Thursday
Friday
Blank row
Monday
Tuesday
Wednesday
Thursday
Friday

From your screenshot, C3 has "$176.77". But looks like you have some rows not shown. What I was asking is this cell. Do you want to copy it over?

Also, I'm a bit confused. From your screenshot, it looks like you want:

Monday
Tuesday
Wednesday
Thursday
Friday
Blank row
Weekly Gains
Blank row
Blank row
Monday
Tuesday
Wednesday
Thursday
Friday
Blank row
Weekly Gains

(I added two blank rows between each week.)

So, what do you want to do with the "Weekly Gains" row?

Yes, just copying weekly gains text over, in column A its only text.

So here is an image of this week and next week.

Screen Shot 2020-09-11 at 3.36.07 PM

The Monday date of the new week now includes the following formula:
=WORKDAY(A100,5,)

I see more clearly what you want.
You want to start the new "Monday" cell two rows below the original "Weekly Gains".
You also want to cut the "Weekly Gains" and paste it two rows below the new "Friday" cell.
The formulas in column C should also be copied to the new week.

This is the best I can get. But again, the problem I mentioned above still exists. Setting the System Clipboard to a random text seems to help a bit. But after 4 runs, the trouble hits in. I have no idea why it is so inconsistent! I'm attaching both the Excel file and the Macro for anyone who would like to help out. Why does it work for the first four times but break at the fifth time (see gif below)? :exploding_head::exploding_head::exploding_head:

Excel2.zip (9.5 KB)

Adding 0.5 pause before inserting the date text to cell seems to have fixed the problem.

image

Here is a macro that I came up with based on the macro you shared. I couldn't get yours to paste in the appropriate cells in my Excel.

ExcelInvestment Project Macro (v9.0.6)

Investment Project.kmmacros (12 KB)

I’m wondering why you’re not just pasting a CSV file in. (Yes, it probably would be fiddly to bring up and manipulate the import dialog. That’s something I need anyway so I might work on it.)

You did not share your excel file. I made one based on your screenshot. It is not surprising it did not work out well with your file. I’m glad you can modify it to suit your need.

So I did actually create a “paste from CSV into Excel” macro, respecting commas.

I don’t know if anyone would want it. (Then I went on to create some Excel graphing macros.)

Here you go, my apologies.

Forum Example.zip (45.3 KB)