• 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.

Moving data from one sheet to another if it meets a certain criteria.

J

jnash

New Member
Hi,

I am trying to create some code that will move data from one worksheet to another if it meets a certain criteria.The criteria will be a product category and a week number. If possible the week number will change incrementally and on a weekly basis. For example this week is week number 37 when I run the code next week the 37 will increase to look for week 38. (I am using excel 2013)

So far I have been using auto filters and haven't had much luck, I have also used an if and copy code (posted below)

Auto filter code:


Code: 
ActiveSheet.Range("$A$2:$X$300").AutoFilter Field:=26, Criteria1:="Bath"
ActiveSheet.Range("$A$2:$X$300").AutoFilter Field:=2, Criteria1:=“37”


Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


Sheets("Bath Data").Select
Range("A1").Select

ActiveSheet.Paste

If and copy code:


Code: 
Dim i, LastRow

LastRow = Sheets("Units YTD").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow

If Sheets("Units YTD").Cells(i, "Z").Value = "Bath" Then ....

I also want the code to paste the new data below the previous data when it is pasted into the new sheet.

to achieve this I was using this code:


Code: 
Sheets("Units YTD").Range("A" & i).Copy Destination:=Sheets("Bath Data").Range("A" & Rows.Count).End(xlUp).Offset(1)

Could the combination of the three parts of code I have pasted here be used to achieve the outcome I want ?

or is there a new code I could use ? (I am very new to VBA so I feel I could be missing something obvious)

Thanks in advance for your help, apologies if the format of my post does not follow the guidelines or it is confusing.
 
Hi,

Thanks for your reply

Apologies for not attaching a sample file, I have attached one now.
 

Attachments

  • Moving data (sample file).xlsm
    22.7 KB · Views: 14


As I saw your code copying data to another tab,
so in fact is it just to Copy / Paste or to really move data ?

​
 
Try this AutoFilter way (paste code to source worksheet module) :​
Code: 
Private Sub CommandButton2_Click()
    Dim Rg As Range
    W$ = InputBox(vbLf & vbLf & " Week # ?", "  Copy Data")
    If W = "" Then Exit Sub
With Cells(1).CurrentRegion
    Set Rg = .Rows("2:" & .Rows.Count)
        F$ = Rg.Columns(1).Address
    If Evaluate("COUNTIF(" & F & "," & W & ")") Then
        Application.ScreenUpdating = False
            F = "SUBTOTAL(103," & F & ")"
            .AutoFilter
            .AutoFilter 1, W
        For N& = 2 To Worksheets.Count
            .AutoFilter 7, Split(Worksheets(N).Name)(0)
            If Evaluate(F) Then Rg.Copy Worksheets(N).Cells(Rows.Count, 1).End(xlUp)(2)
        Next
            .AutoFilter
        Application.ScreenUpdating = True
    End If
End With
    Set Rg = Nothing
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Thanks Marc, this works great on the test file.

Now I want to implement this on to this real file it does not appear to work.

What sections of the code do I need to adapt to make sure it copies the correct data ?

Please be aware the real file is very large and goes from A to Z and down to 40,000 and will keep expanding throughout the year.

Thanks in advance
 
Ok thanks, but exactly what part of the code do I need to change ?

As I explained in my original post, I am very new to VBA so I don't understand what elements I need to change to adapt it to my real file.

Could you highlight what parts I would need to change.

Thanks
 
Maybe codelines #5, 6, 7, 12, 13, 14 and 15 :
any reference to an object like worksheet, column, row, range or cell …

it's weird to not post an exact reflect sample of real workbook !
When you compare both, what are the differences ?
​
 
the reason why I didn't post an exact replica is because the real document is 26 columns wide and 40,000 lines down, it also has very sensitive data in there.

I have looked at the code and have got everything working now except for one piece, the code only seems to copy kitchen data and not the bath and bed data.

I think the issue is in this piece of code:
Code: 
For N& = 3 To Worksheets.Count
          .AutoFilter 25, Split(Worksheets(N).Name)(0)
          If Evaluate(F) Then Rg.Copy Worksheets(N).Cells(Rows.Count,1).End(xlUp)(2)
        Next
            .AutoFilter

Do you have any ideas why this problem occurs ?
 
That doesn't seem to affect it, the tab name and the criteria don't seem to need to match.

Could you explain what the above piece of code is trying to do, I understand it is putting a filter on and evaluating (F). How did you define F ?

And how is the code pasting the values when no Paste is written in the code.

Thanks for your help and patience with this issue !
 

Well read the code :
• looping on each tab and name is a filter criteria !
• F variable is defined above (#7 & 10) to check if data match tab name !

Place a break point on codeline and check variables in Locals window …
​
 
You must log in or register to reply here.
Back
Top