Macro to paste and delete

[bertlogdi1]

I hope someone can help with a macro that will solve this:
I have copied 7 sets of data from a website. The first data is the main and largest data. This will be pasted first and then the other 6 data sets (each is a table) will be used to increase the length of the table starting from the bottom up and at different rows.

The first will be pasted into cell A1. Then the next (first of the remaining 6) will start from row 220, then the next will start from row 166 and so upwards till the last is pasted.

There is a formula in column P that will check the rows and output an error for incomplete data in the relevant cells or will output either of "N" or "OK" as the case may be. The cells with either "N" or "OK" will now form a block of cells under column P surrounded above and under by those with errors in column P. It is hoped that if there are less than 6 "OK"s in each block of cells in column P, the data in the first"OK" row in that block, is used to populate enough row(s) as to now have 6 rows with OK under column P in that block.

Then the macro will delete any row that still has a blank under column A or has "N" under column P. It will not delete those with errors under column P unless they are blanks under column A.

I hope this is not confusing.
Regards

[Mordred]

If you already have a workbook for all of this then it may be best to upload it (with mock data) along with the data to be transferred and the desired outcome.

[bertlogdi1]

If you already have a workbook for all of this then it may be best to upload it (with mock data) along with the data to be transferred and the desired outcome.

Hi, Thanks for your reply. The workbook is virtually empty and awaiting these data that I have copied from the website into clipboard so it is difficult or should I say I do not know how to upload the clipboard with the data in it to this forum. So what I can upload is the finished outcome done manually. Will that be helpful?

The website does not hold the info in html hence I cannot import them. It appears they are java delivered but then I can copy them manually. While I can manually paste the main page data to cell A1 and get a table covering columns A:O, this does not work for the rest of the 6 data sets which I copied from links that pop up off the website. If any of these 6 are pasted by clicking the data as you would off a clipboard, it pastes everything into one cell; thus it requires use of paste special and the selection of text for the table to matchup with the first table.

May be I should clarify that the pasting is to go to the opened excel which has only two entries:
Formating of column M to receive fractions and a formula in cell P1 which will be autofilled down later by the macro.

So my expectation is that the macro will paste the data from the clipboard. As I am new to excel and VBA I do not know how to make excel remember the sequence of copying into the clipboard and then pasting them sequentially into the appropriate rows before continuing with the tranformations.

[bertlogdi1]

I am going to explain this as detailedly as I can. I hope someone can help with a macro that will solve this:
I have copied 7 sets of data from a website. The first data is the main and largest data. This will be pasted first and then the other 6 data sets (each is a table) will be used to increase the length of the table starting from the bottom up and at different rows.

The first will be pasted into cell A1. Then the next (first of the remaining 6) will start from row 220, then the next will start from row 166 and so upwards till the last is pasted.

There is a formula in column P that will check the rows and output an error for incomplete data in the relevant cells or will output either of "N" or "OK" as the case may be. The cells with either "N" or "OK" will now form a block of cells under column P surrounded above and under by those with errors in column P. It is hoped that if there are less than 6 "OK"s in each block of cells in column P, the data in the first "OK" row in that block, is used to populate enough row(s) as to now have 6 rows with OK under column P in that block.

Then the macro will delete any row that still has a blank under column A or has "N" under column P. It will not delete those with errors under column P unless they are blanks under column A.
The workbook is virtually empty and awaiting these data that I have copied from the website into clipboard so it is difficult or should I say I do not know how to upload the clipboard with the data in it to this forum.
The website does not hold the info in html hence I cannot import them. It appears they are java delivered but then I can copy them manually. While I can manually paste the main page data to cell A1 and get a table covering columns A:O, this does not work for the rest of the 6 data sets which I copied from links that pop up off the website. If any of these 6 are pasted by clicking the data as you would off a clipboard, it pastes everything into one cell; thus it requires use of paste special and the selection of text for the table to matchup with the first table.

May be I should clarify that the pasting is to go to the opened excel which has only two entries:
Formatting of column M to receive fractions and a formula in cell P1 which will be auto filled down later by the macro.

So my expectation is that the macro will paste the data from the clipboard. As I am new to excel and VBA I do not know how to make excel remember the sequence of copying into the clipboard and then pasting them sequentially into the appropriate rows before continuing with the transformations.

I hope this is not confusing.

Regards