Copy Paste number of rows using input from user

[Cmore77]

Hi all,
I have been struggling with a problem for a while and I believe VBA can help, but unfortunately my knowledge of excel VBA is limited. Anyway here is the problem:

I have an excel file where users need to input sample data per row for a fridge storage system. This Inputsheet will be used to upload data into the storage systenm using a script. This script reads rows which contain data. In the sheet where the user is adding his data (called Inputsheet) I have formulas and data validation based on lists included. To make sure the cell values are correctly included in the other program, I have included them in rows 1-100. If the user is adding data of just 20 samples, the script still reads the formulas in rows 21-100 and therefore adds incomplete data.
What I would like to see is that the user is asked (using an input box?) to indicate how many samples he wants to add (say 20) and that Excel copies the row (including the prefilled formulas and validation listst) from Sheet X to the Inputsheet which will then be pasted 20 times starting in cell A1.

Thanks for any help.
Rob

[Arkadi]

Cmore77, are you able to provide a sample workbook illustrating what you want?

[JOHN H. DAVIS]

Would something like this help to get you started?

Please Login or Register  to view this content.

[Cmore77]

I have attached the file. A short explanation:
- Inputsheet: row 3 contains the formula and dropdown selections
- FIS export: this is the format necessary for the script to upload it in the fridge information system (FIS). The information is derived from the Inputsheet and slightly modified to allow the script to put the information in the correct database fields.
The other 2 sheets contain the formula and validation fields for the Inputhseet and teh FIS export sheet.

the steps:
- Inputsheet and FIS export are blank, only headers are shown
- input number of samples by user (eg.20)
- Row 3 is copied from "Formula Tab Inputsheet" and "Formula Tab FIS export" and copied 20 times to resp. "Inputsheet" and "FIS export"
- user completes details of 20 samples in Inputsheet (rows below R22 do not contain values or formulas or validation cells)
- template is saved and uploaded to Fridge Information System manually.

I hope this helps...

[Arkadi]

Cmore,

When you delete all the content from Inputsheet, the formulas in Formula Tab FIS export gets ruined. This is what I suggest:

1. on your sheet Formula Tab FIS export, edit the formula, removing the "=" so that it is now just text... for example A1 would just have the text "Inputsheet!A3" Do this for the whole of row 3.
then the code below will do what I think you want:

Please Login or Register  to view this content.

[Cmore77]

Thanks Arkadi,

this helps to some extent. I receive an error since not all cells can be switched back from text to formula in Tab FIS Export. eg. Cell E3 contains a different formula, so the VBA stops there...

[Arkadi]

I was at work when I worked on this so I dont have the file with me. When I tested the code I sent on the workbook it worked. Ill upload my version and we can see what is causing your error.

[Arkadi]

Here is the file I used... I got no errors. Can you check it out and see if it works for you? Module 2 contains the macro I prepared.

[Cmore77]

Thanks Arkadi, your file works! Maybe I did something wrong when replacing the formulas with text. Anyway, this does the trick for me! And more importantly, it gives me more insights on how VBA works. Thanks a lot for your efforts

[Arkadi]

My pleasure Cmore
Please remember to mark the thread as solved if we are done? Bedankt!