Need a macro

[RSEKAR]

Dear Sir,
I am in need of a macro to satisfy the following requirements. I do not know whether it is possible. I have two sheets in my work book named as “SELL” and “SELL1”. I copy some of the data from the SELL sheet and paste (as paste special - values) in the SELL1 sheet.
The SELL sheet is filtered on some criteria. The SELL1 sheet is not filtered.
The coping has to be done from particular columns in the SELL sheet. The data should be pasted in the SELL1 sheet in particular columns.
In SELL sheet the filtering starts from 10<SUP>th</SUP> row and copying should be done from the 10<SUP>th</SUP> row. Pasting should be done in the SELL1 sheet starting from 10<SUP>th</SUP> row. From the SELL sheet B column data should be pasted to A column of SELL1 sheet. Similarly the data from G, AT, AJ columns (from SELL sheet) to be pasted to B, C, D columns of SELL1 sheet. The selection always starts from 10<SUP>th</SUP> row. The problem is the extent of selection for coping will vary starting from 10<SUP>th</SUP> row for each cycle. There will be data below the selection. Hence the entire column can not be selected for copying. Once the selection is done in the B column manually, the same extent of selection should be used in the other columns also for copying. So let me select the cells in the B column of SELL sheet first and make the macro to run. The macro should copy the data from the selected cells and paste the data in the A column of SELL1 sheet. Now the macro should continue using the same extent of selection for copying from the other columns one by one from SELL sheet and paste in the SELL1 sheet. The same extent of selection should be used in different columns (G, AT, AJ) in the SELL sheet for copying.
. All the pasting should be done as paste special – values. The extent of selection should be taken as standard from the B column where I select the cells manually.
Now I do coping and pasting manually. My work will be made easier if I get a macro to copy and paste the data from SELL sheet to SELL1 sheet.
Thanking you Sir,
Yours truly,

 

[Peter_SSs]

Perhaps I haven't fully understood, but I don't think you should have to do anything manually.

One thing I'm not quite clear on relates to the first row of the filtered data on SELL sheet. You say row 10 but I don't know ..

1. If the filter headings are on row 10 or the first row of actual data is on row 10 with the headings in row 9. Which is it?

2. Are the filter headings to be copied to SELL1 or just the data below the headings? (It would be easier if the headings were copied as well).

Anyway, this is a start. Test it in a copy of your workbook.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Base</SPAN> 1<br><br><SPAN style="color:#00007F">Sub</SPAN> Copy_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> rFltr <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> SrcCols, DestCols<br>    <SPAN style="color:#00007F">Dim</SPAN> cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    SrcCols = Array("B", "G", "AT", "AJ") <SPAN style="color:#007F00">'<--SELL columns</SPAN><br>    DestCols = Array("A", "B", "C", "D") <SPAN style="color:#007F00">'<--SELL1 columns</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> ws = Sheets("SELL")<br>    <SPAN style="color:#00007F">Set</SPAN> ws1 = Sheets("SELL1")<br>    <br>    cols = <SPAN style="color:#00007F">UBound</SPAN>(SrcCols)<br>    <SPAN style="color:#00007F">Set</SPAN> rFltr = ws.AutoFilter.Range<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ws1<br>        <SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> cols<br>            Intersect(rFltr, ws.Columns(SrcCols(c))).Copy<br>            .Cells(10, DestCols(c)).PasteSpecial Paste:=xlPasteValues<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>