Copy / Paste VBA


New Member
Jun 5, 2015
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.


Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".


Well-known Member
Nov 1, 2008
Office Version
  1. 365
  1. Windows
  2. MacOS
<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'</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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back