Copying rows from one sheet
to another can be done in Excel. The worksheet should be saved to contain values in the
CSV or Common Separated Values format
can be used to copy a row to a new worksheet if a particular condition is true.
is a scripting tool that can be used to write small and efficient programs to
automate repetitive tasks
. It's available for free and can be downloaded from the internet. Biterscripting is particularly useful for writing scripts to
automate copying of rows
from an Excel Office Software worksheet to a new worksheet.
I have a list of manuals on worksheet 1 and I am trying to copy info to a new sheet based on request dates.
So, let's say column J is request date.
If a date exists in cell J2, copy row 2 and paste in worksheet 2.
If a date exists in cell J5, copy row 5 and paste in worksheet 2.
... and so forth. Solution
Save the file worksheet in CSV (Comma separated values) to, say, C:/X.csv.
Run the following script in biterscripting. var str data, line, cell ; cat "C:/X.csv" > $data
# If a date exists in cell J2, copy row 2 and paste in worksheet 2. (Cell J is cell 10.) lex -p -e "2" $data > $line ; wex -p -e "10" $line > $cell if ( $cell <> "") echo $line >> "C:/Y.csv" endif # If a date exists in cell J5, copy row 5 and paste in worksheet 2. (Cell J is cell 10.) lex -p -e "5" $data > $line ; wex -p -e "10" $line > $cell if ( $cell <> "") echo $line >> "C:/Y.csv" endif
# ... and so forth.
The resulting worksheet is in C:/Y.csv .
Hope this helps.
If you don't have Biterscripting, get it free. It is very useful for automating a lot of things. Install it as follows:
I forgot one thing, add the following at the beginning:
cat "" > "C:/Y.csv" # Empty out the output file from last time.
set $wsep = ","
The sys variable $wsep dictates what separates cells. By default, it is set to space, tab, etc. So, currently, it is probably separating cells at spaces, etc.
To automate, put the entire script in a file, say, C:/Z.txt, then call the following
You can also call Biterscripting from any other program (including a web server) with this script as follows:
Of course, when original CSV file in X.csv is changed, you would have to run the script again to recreate Y.csv.
One thing I did not understand, did you only want rows 2 and 5, or did you want to do this on every row?
for this tip on the forum.
Latest update on November 6, 2016 at 02:11 PM by