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

VBA Help - to copy the data into new sheet

R

rtyagis

New Member
I am working on a data sheet where I need to copy the complete row when there is 'CR' or 'blank' data in Column 'C' (Type).

Sheet 1-
Code: 
Account Date Type Narrative Value Date Payments Reciepts
300002-0113 20/09/2014 CHQ TFR 300002 5
300002-0113 21/09/2014 CR 10
22/09/2014 DR TFR 300002 50
300002-0113 23/09/2014 CHQ 100
22/09/2014 TFR 300002 30
300002-0113 21/09/2014 CR TFR 300002 10

Copy the same above data row when there is 'CR' in Column 'C' (Sheet 1)

The data in the sheet 1 is added/updated regularly, so for e.g. - we have data till yesterday 20/09/14. We ran the macro on 20/09/14. It will give us the result of upto 20/09/14 and the same will be copied in the Sheet 2.
VBA Code
Code: 
Sub cr()
Dim i As Long
For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Cells(i, 3) = "CR" Or Cells(i, 3) = "" Then
        Rows(i).Copy Sheets("UNCLEARED & QUERY ITEMS").Range("A" & Rows.Count).End(3)(2)
    End If
Next i
End Sub

If we add more data in sheet 1 after 20/09/14; then it should pickup all the cells where data is added after that date e.g. 21/09/14 onwards and copy the data in the new sheet. But currently it's again copying all the data again means the data of 20/09/14 and lateron data.
Could some one help me to change the above VBA code to do the above task.

Thanks,
RT
 
Sample Excel File attached with VBA Query.

Edit - I don't have access to upload the file.
 
Last edited:
Hi,
Just clear the old data from sheet before pasting new one.

Code: 
Sub cr()
Dim i As Long
Sheets("UNCLEARED & QUERY ITEMS").Cells.Clear
For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Cells(i, 3) = "CR" Or Cells(i, 3) = "" Then
        Rows(i).Copy Sheets("UNCLEARED & QUERY ITEMS").Range("A" & Rows.Count).End(3)(2)
    End If
Next i
End Sub
 
This is my weapon of choice when faced with this problem. No looping.

Code: 
Option Explicit
 
Sub FilteritBetter()
    Sheet2.[a1].CurrentRegion.Offset(1).ClearContents
    Sheet1.Range("C1", Sheet1.Range("C" & Rows.Count).End(xlUp)).AutoFilter 1, "CR", xlOr, ""
    Sheet1.[a1].CurrentRegion.Offset(1).Copy Sheet2.[a2]
    Sheet1.[c2].AutoFilter
End Sub

Take care

Smallman
 

Attachments

  • FilterEasy.xls
    39 KB · Views: 15
Hi Small Man and Deepak,

Many Thanks!

I would like to go with the Small Mans approach

Small Man a small query for you -

Both the above sheets are in the middle of an excel sheet Means they are "Tab 6" and "Tab 8" and named as below:

Sheet 1 Name is "Input LL"
Sheet 2 name is "UNCLEARED & QUERY ITEMS"

Could you please tell me how to amend your macro to add above names of sheet and also tell how to add that button on Sheet 1 as per you example sheet.

Thanks Again,
RT
 
Hi @rtyagis

If you use the sheet name in your coding you quickly run into problems which the sheet name changes. I try and always use the sheet code name when ever specifically refering to a sheet. The following is the sheet code name


SheetCodeName.PNG


You see in the above I have 4 sheets. To the right of each sheet we have this construct

Sheet1(Test)
Sheet2(Data)
etc.

The sheet code name for Test is Sheet1, for Data it is Sheet2. You get the idea. The beauty of using the sheet code name is the code name won't change (unless you callously break into the back end of VBA and change it) now that is less likely than a user simply deciding that your sheet naming convention is rubbish and they want to change a sheet name on you. Now your code won't run. Smart coders will avoid this by using the Code name and as the sheet name changes their code will still run.

Hope that helps.

Take care

Smallman
 
rtyagis said:
Hi Small Man and Deepak,

Many Thanks!

I would like to go with the Small Mans approach

Small Man a small query for you -

Both the above sheets are in the middle of an excel sheet Means they are "Tab 6" and "Tab 8" and named as below:

Sheet 1 Name is "Input LL"
Sheet 2 name is "UNCLEARED & QUERY ITEMS"

Could you please tell me how to amend your macro to add above names of sheet and also tell how to add that button on Sheet 1 as per you example sheet.

Thanks Again,
RT
Click to expand...


I did nothing so far in this just added below line!!

Sheets("UNCLEARED & QUERY ITEMS").Cells.Clear
 
@Deepak

This bit:

I did nothing so far in this just added below line!!
Click to expand...

I strongly disagree. You gave your time to help someone who asked. You saw a gap in the coding and you corrected it. You did all anyone could ever ask.

Take care

Smallman
 
Hello Small Man,

I have changed the name as e.g. Sheets("UNCLEARED & QUERY ITEMS")
But when I am running the macro I am getting following error "Application-defined or object-defined error"

I have just copy pasted your Macro and changes the sheet name as above.

Am I missing something?

Sorry for a very basic query.
 
Got the problem - the issue and I think is because the macro is recorded at the worksheet level. So to change it go to the modules node on the VBA, then paste your macro in the new module (make sure you delete the one recorded at the worksheet level).

Thanks everyone.
 
Hi Guys!

Is it possible to add a date from which it will copy the data of only those dates and after that date from Sheet 1 to Sheet 2?

Thanks,
 
Hi @Deepak and @Smallman,

I need your help in amending the macro, you guys provided.
Sample file "Tool" attached.

I just wanted to add one more parameter to it, if I want to copy data from a date example 13/10/2014 this macro will pick data from that date onward and leave all the data when is before that date. If possible can this date will be picked from the cell (user specified) then it will be very useful.

Thanks Again!
RT
 

Attachments

  • Tool.xlsm
    91.1 KB · Views: 3
In continution to @Smallman approach!!

Code: 
Option Explicit

Sub FilteritBetter()
Dim fdate As Double
Application.ScreenUpdating = False

    With Sheets("UNCLEARED & QUERY ITEMS")
        .[A1].CurrentRegion.Offset(1).ClearContents
    End With
   
    With Sheets("Input LL Chq Payment Data")
            fdate = .[L2] 'date after data will copy as required
           
            With .Range("A1").CurrentRegion
                .AutoFilter 3, "CR", xlOr, ""
                .AutoFilter 2, ">" & fdate
                .Offset(1).Copy Sheets("UNCLEARED & QUERY ITEMS").[A1]
            End With
           
        .[A1].AutoFilter
    End With
Application.ScreenUpdating = True
End Sub
 
Hi @Deepak,

I don't know but the above macro is not working properly.
1- it's copying data from A1 not from A2.
2- It's not picking up date from L2

Please see attached Tool Sheet and tell me, where I am doing wrong?

Thanks,
RT
 

Attachments

  • Tool.xlsm
    91.2 KB · Views: 3
I didn't find any issue so far as same was tested before posting meanwhile i rechecked.

upload_2014-11-4_15-38-1.png



upload_2014-11-4_15-38-36.png



Pls share the screen shot where u having the issue.
 
Many thanks @Deepak !

It worked now, I don't know why it was not working last time. May be I am new user so missed something.

Thanks again for your quick help on this!
RT
 
You must log in or register to reply here.
Back
Top