Submitting date from one sheet to another

Good Morning

I am currently in the middle of trying to submit data from one excel sheet to anothewr, adding the data to seperate rows. This is the current VBA code that im using:

Please Login or Register  to view this content.

Current Setup of my workbook looks like this:

Capture11.JPG
Capture12.JPG

Im wanting the data that is input on the first image, to be copied into the sepate page, without overwriting any other data that has been submitted.

I am encountering an error

"Run Time-Error"9""
Subscript out of range

Any help on how to solve this would be great.

Regards

Looks like the only thing that could give you a subscript out of range Error is the output tab name. Could there be a trailing space on the tab name? I'd be inclined to qualify the input ranges with the sheet name, for example, Sheets("Home").Range("H10").Value

Please don't change the original post ... it completely screws the context of subsequent responses.

If you're going to change the code to something else that doesn't work, do it in a separate post.


Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Looks like you spelt ws_output wrong. See: ws_ouput = "TestD" and ws_ouput = "Data"

Use Option Explicit to check for missed or misspelt variable names.

Both these versions work

Please Login or Register  to view this content.

And ...

Please Login or Register  to view this content.

@jolivanes: the input cells are in alternate rows ...

Might be doable with SpecialCells

Had a try but I can't get it to work.

Here's my sample workbook to play with.

I am out of the door. Happy Hour is calling.
Should work with copy and paste special.

Please Login or Register  to view this content.

@DingaBell: you now have four versions that all appear to give the correct results plus an explanation of the root cause of the problem. Also, by referencing the Home sheet specifically, the code will work whatever sheet is selected (if you run it manually, not from the button.

Is this now resolved


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.