Code to copy selection of rows from master into template file

[BigDxwg]

Hi all,

My first post so apologies in advance if I am not following any of the correct protocol in anyways.

I am working off a master sheet and coping a selection of rows based on filter into a template file.

Considering I am quite inexperienced with macros, I have managed to do fairly well using the Record button in EXcel. The code can do with some tidying up.

Master Spreadsheet contains

COL A Batch No
COL B Unique ID
COL C Account
COL D System
(There are a few columns in between but I do not need these for copying into template.)
COL L Type
COL M Location
COL N AccountDescription
COL O Owner Email

The template that I am copying to contains
Sheet 1 - General Information, How to complete the form in Sheet 2
Sheet contains:
COL A Unique ID
COL B Account
COL C System
COL D,E,F,G,H are a mixture of free text and drop downs that the user will have to fill in and are shaded yellow.
COL J Type
COL K Location
COL L Account Description.

Hope that helps you understand sheets I am working with.

• On the master I manually filter by OwnerEmail and assign a Batch No that helps me identify them. 1 owner could have anything from 1- 280 accounts.
  Once filtered, I would copy / paste Col B,C,D from Master, Paste into Col ABC of Template
  copy/past Col LMN into JKL.
  Col A is copied into sheet as white text - this used for the naming of the file.

I have managed to get all that working with the Record, however as the number of rows copies varies from owner to owner, I have to manually tidy up COL D,E,F,G,H on template.

Ideally i would like your assistance to achieve 2 things - put in a macro code of some sorts that will recognise empty rows and delete, and where there is surrounding data in ABC, and JKL of template it will format the sheet with drop downs as above. This I think is probably quite easy to achieve, I just dont know how which is why i am here.
Secondly if there is way to tell the macro, for each batch id - run this macro. that will be amazing!

Also sorry I have been unable to post up real data as I am working with confidential data at work.

Thanks in advance

[davesexcel]

Can you post a sample workbook?

[Zer0Cool]

Copy and paste is not a very efficient way to move data like this. It will do in a pinch but its slow and can be buggy, especially as the amount of data increases.

A step up from this would be to use something like a For loop to loop the cells (range objects) in a given column/row and set the destination range.value property to the value from the source range.value. This is still slow but should work faster and be easier to modify than recorded copy/paste or even using copy/paste methods.

From what I can gather from your post, your source and destination are also not apples to apples in terms of layout. There are columns you mention in one but not the other. This makes it harder to move content in bulk instead of 1 cell at a time.

If your source and destination where identical column layouts you could simply store the source range in an array and then dump that array into a source range on the destination sheet. This is by far the quickest method to move data, but only works when the source and destination are the same dimensions.

Its hard to provide specific advice without a sample. From the sounds of it your not a programmer. So its likely any generic input wont help you much.