Copy / Paste VBA

[UserS1]

Hi All

Bit of a newbee at VBA and giving it a go. I have searched the internet and also here but can't find exactly what I'm looking for. I have found similar, however I can't seem to modify it correctly.

I have a sheet "703Data 1" that is updated automatically with data and doesn't have a fixed length. There is data in Col 'AS' of three different types being, "" (blank), " ->" or data that is not always the same. If the data in col "AS" is "" (blank) or " ->", then I need to skip over it and only detect the remaining data. When it's detected, then I need to copy itself and the next 5 cols on the same row (if it found the data on row 4, then it would copy AS4, AT4, AU4, AV4, AW4, AX4) to another sheet called "Labour" starting a D6 and going down from there. Before it copies the dta it would clear the previous data on the Labour sheet.

Would love some help with the code.

Thank you.

Luke

 

[sijpie]

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'http://www.mrexcel.com/forum/excel-questions/859889-copy-paste-visual-basic-applications.html</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> GetData()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsIn <SPAN style="color:#00007F">As</SPAN> Worksheet, wsOut <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> rIn <SPAN style="color:#00007F">As</SPAN> Range, rOut <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lMax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">' lets use some friendly names to help us</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsIn = Sheets("703Data 1")<br>    <SPAN style="color:#00007F">Set</SPAN> wsOut = Sheets("Labour")<br>    <br>    <SPAN style="color:#007F00">'set start of output range and clear existing data over 6 columns</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rOut = wsOut.Range("D6")<br>    rOut.Resize(rOut.CurrentRegion.Rows.Count, 6).ClearContents<br>    <br>    <SPAN style="color:#007F00">' looping through each row in the input data is very slow _<br>      for large datasets, but it is clear what we are doing</SPAN><br>    <SPAN style="color:#007F00">' get last row number</SPAN><br>    lMax = wsIn.Cells(Rows.Count, "AS").End(xlUp).Row<br>    <br>    <SPAN style="color:#007F00">' now loop through each cell in column AS</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lR = 1 <SPAN style="color:#00007F">To</SPAN> lMax<br>        <SPAN style="color:#00007F">Set</SPAN> rIn = wsIn.Cells(lR, "AS")<br>        <SPAN style="color:#00007F">If</SPAN> rIn <> vbNullString And rIn <> "->" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' 'copy' the data by setting the values the same. _<br>              This is very fast (unlike copy/paste)</SPAN><br>            rOut.Resize(1, 6).Value = rIn.Resize(1, 6).Value<br>            <SPAN style="color:#007F00">' move output range one down</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rOut = rOut.Offset(1, 0)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lR<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


If your input data is really large then I can modify the code to make it run a lot faster. But this code is pretty clear in what it is doing.
The fast alternative is to read the columns AS:AX into an array and then check the rows that need to be copied in the array in memory (rather then reading each time from the sheet), then copying the required rows into another array and writing the output array in one go. Reading from and writing to the sheet is relatively slow.

Read also the oher comments in the code to better understand it.

 

[UserS1]

Thanks sijpie!! and thank you for inserting comments.