• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying from worksheet and append to other worksheets based on criteria [Solved]

S

Shan

New Member
I have a worksheet called “Rawdata”. I also have several worksheets named after account numbers eg 3101,3121,3131 etc.


Every month I need to filter the ‘Rawdata’ using account number as criteria, and then APPEND to respective account sheet (as I don’t want to paste over previous months data). I need to individually do this for every account number (there’s over 60).


How do I use VBA to do this?
 
Hi Shan


What I suggest you do is and I will step through the process;


. Run an Advanced filter on your Acc Numbers push a unique list to one side.

. Iterate through these account numbers copying each block of values with a filter to each sheet.


Yep that is about it. I should be able to knock something up. Woop there it is....

[pre]
Code: 
Option Explicit
Sub MoveData()
Dim i As Integer
Range("A1", Range("A65536").End(xlUp)).AdvancedFilter 2, , [E1], 1

For i = 2 To Range("E65536").End(xlUp).Row
Range("A1", Range("A65536").End(xlUp)).AutoFilter 1, Range("E" & i)
Range("A2", Range("D65536").End(xlUp)).Copy Sheets(Range("E" & i).Value).Range("A65536").End(xlUp)(2)
Next i
Columns(5).clearcontents
End Sub
[/pre]

Give the above a try. I assumed you have only 4 cols but this can be changed to suit. Also used Col 5 as a bit of a dumping ground. Should be able to post a file to go with it if you can’t get it going.


Take care


Smallman
 
Could you please post a file, I'd really appreciate that as I haven't had much VBA experience. Thank you.
 
Hi Shan


No worries I can't from my current computer but will be able to do so in an hour or so.


In the mean time you could dump that code in a regular module, put some dummy data in the first 4 Cols of a fresh workbook and make Col A equal to a the sheet names you want to send your data to. That is a quick way to see a working copy as this will all I will do when I get to my home computer.


Take care


Smallman
 
Oh, thank you so much. I tried and it worked, you are a champ!!!


I am trying to understand your script but have got a few gaps in my understanding. Are you able to explain:

- what the advancedfilter 2,[E1], 1 does?

- what is column E used for and why isn't is clearing contents?

- If my workbook doesnt contain all account numbers set up as worksheets (there may be 200 account numbers but I only have specific accounts set up as worksheets ~60), will this come up as error?

Am I able to declare the "Rawdata" worksheet, and the array of sheets data need to be pasted to (ie the 60 account numbers).


Thanks so much. You've been a great help.
 
Hi Shan


The advanced filter is a handy tool that allows all manner of data manipulation. Play around with it in native XL.


Col e is a dumping ground for unique characters. It is a list of accounts to iterate through. I clear the contents after the code is run as it is not required any longer.


Ah I suspected you would have unaccounted for sheets. I won't now be at a computer for some time. Will address this issue later.


Take care


Marcus
 
Hi Shan


The advanced filter is a handy tool that allows all manner of data manipulation. Play around with it in native XL.


Col e is a dumping ground for unique characters. It is a list of accounts to iterate through. I clear the contents after the code is run as it is not required any longer.


Ah I suspected you would have unaccounted for sheets. I won't now be at a computer for some time. Will address this issue later.


Take care smallman
 
Hello Smallman


I changed the code to include pasting data from columns A:L which worked. However how do I change it to paste data from row 11 down, not from row 2.


Also this code stops when it contains data for an account number for which no worksheet has been set up. Were you able to find a solution for this?


Code: 
[pre][code]Sub MoveData2()
Dim i As Integer
Range("A1", Range("A65536").End(xlUp)).AdvancedFilter 2, , [M1], 1

For i = 2 To Range("M65536").End(xlUp).Row
Range("A1", Range("A65536").End(xlUp)).AutoFilter 1, Range("M" & i)
Range("A2", Range("L65536").End(xlUp)).Copy Sheets(Range("M" & i).Value).Range("A65536").End(xlUp)(2)
Next i
Columns(13).ClearContents
End Sub
[/pre]

[CODE END]
 
Hi Shan


To address the issue where you do not have a sheet for the items in the list you create use this.

[pre]
Code: 
Option Explicit

Sub MoveData2()
Dim i As Integer
Range("A1", Range("A65536").End(xlUp)).AdvancedFilter 2, , [M1], 1

On Error Resume Next
For i = 2 To Range("M65536").End(xlUp).Row
Range("A1", Range("A65536").End(xlUp)).AutoFilter 1, Range("M" & i)
Range("A2", Range("L65536").End(xlUp)).Copy Sheets(Range("M" & i).Value).Range("A65536").End(xlUp)(2)
Next i
Columns(13).ClearContents
On Error GoTo 0
End Sub[/pre]

For the issue where you want to paste your data into Row 11 use this


Range("A2", Range("L65536").End(xlUp)).Copy Sheets(Range("M" & i).Value).[a11]


Hope this helps


Take care


Smallman
 
You might also want to have a look at this post by Chandoo

http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/
 
Hi Hui


I didn’t know that page existed, so thanks for posting it. I just had a look at the workbook which goes with the blog post. I love that there is workbooks attached to these things, no guess work.


Chandoo might want to have a look at a few things. For larger data sets that puppy is going to take a significant amount of time to execute the code. It has issues on a number of fronts, it selects the sheet it is pasting to and the Data Entry sheet for each individual line. It also uses a custom function to determine the last row for the destination sheet. These things both add time to the efficient running of the code. I am a bit surprised that page is not updated with code to evaluate each cluster of like cells in one smooth movement.


Anyways just my thoughts on the matter. It will be good for Shan to have some options.


Smallman
 
Thank you so much Smallman, it all works now. You've been a great help !! Thank you Hui for that post.
 
Shan


To post code and maintain the codes format put a single ` before and after the code

That is the back tick next to the 1 under the Esc key,

Not the forward tick ' next to the Enter key.
 
You must log in or register to reply here.
Back
Top