Copy Visible Column Rows

[adamsm]

Hi anyone,

I'm trying to figure out a code that would copy visible data rows.

My intention is to copy visible data rows from column G of the sheet "mem" and paste them to the column C of the sheet "invoice".

Note: My data in "mem" starts from row 17. And in "invoice" from row 16 and onwards.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Peter_SSs]

If the hidden rows are hidden as the result of an AutoFilter then try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyData()<br>    <SPAN style="color:#00007F">Dim</SPAN> TgtCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("invoice")<br>        <SPAN style="color:#00007F">Set</SPAN> TgtCell = .Range("C" & .Rows.Count).End(xlUp).Offset(1)<br>        <SPAN style="color:#00007F">If</SPAN> TgtCell.Row < 16 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> TgtCell = .Range("C16")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("mem")<br>        .Range("G17", .Range("G" & .Rows.Count).End(xlUp)).Copy Destination:=TgtCell<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[adamsm]

Thanks for the help.

How should I modify the code if I want to include more columns that needs to be copied from "mem" sheet.

Lets say; Column F to D, J to E, N to F and so on.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Peter_SSs]

Lets say; Column F to D, J to E, N to F and so on.

1. Is there any significance in listing these backwards? That is F to D rather then D to F?

2. Where (which columns) are these disjoint columns to be placed on the invoice sheet? That is which columns do D:F end up in in the invoice sheet? Same for E:J and F:N?

3. Am I correct in thinking the invoice sheet may already have data from row 16 down and this code is to append the new data below the existing data?

 

[adamsm]

Thanks for the reply.

My intention is to copy visible data rows from column F of sheet mem to column D of sheet invoice.

In the same way data rows from column J of the sheet mem to column E of invoice sheet and data from column N of mem sheet to column J of the invoice sheet.

And yes you are correct the invoice sheet already have data from row 16 down and this code is to append the new data below the existing data.

Any help on this would be kindly appreciated.

 

[Peter_SSs]

Ah, I see what you mean by F to D now.

See if this is close to the mark.

<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> CopyData()<br>    <SPAN style="color:#00007F">Dim</SPAN> TgtRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, mLastRow <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>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> mCols, iCols<br>    <SPAN style="color:#00007F">Dim</SPAN> wsm <SPAN style="color:#00007F">As</SPAN> Worksheet, wsi <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> mFirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 17    <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> iFirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 16    <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <br>    mCols = Array("F", "J", "N")    <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    iCols = Array("D", "E", "J")    <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsm = Sheets("mem")<br>    <SPAN style="color:#00007F">Set</SPAN> wsi = Sheets("invoice")<br>    <SPAN style="color:#00007F">With</SPAN> wsi<br>        TgtRow = .Range(iCols(1) & .Rows.Count).End(xlUp).Offset(1).Row<br>        <SPAN style="color:#00007F">If</SPAN> TgtRow < iFirstRow <SPAN style="color:#00007F">Then</SPAN><br>            TgtRow = iFirstRow<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> wsm<br>        mLastRow = .Range(mCols(1) & .Rows.Count).End(xlUp).Row<br>        rws = mLastRow - mFirstRow + 1<br>        <SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(mCols)<br>            .Range(mCols(c) & mFirstRow).Resize(rws).Copy _<br>                Destination:=wsi.Range(iCols(c) & TgtRow)<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>

 

[adamsm]

Thanks for the help. I do really appreciate it. It works fine now.