UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba Help With Macro, Office 2013    
 
   
Sotolin
post Aug 19 2019, 11:26 AM
Post#1



Posts: 89
Joined: 27-August 03



Hi Everyone,

I'm running office 365

I have an excel file that has a list of about 86000 rows and there is information in 1 of the columns that identifies whether the record is existing form a prior period or if it is new.

I need to be able to create a quick macro that takes all the new records into a new worksheet called "New"

Here is the code I am using but I'm getting an error "Object doesn't support this property or method" and I'm not sure that that means... I am really new to vba

When I click Debug, the "ThisWorkbook.Worksheets("This Week").cell(1, 1).Select" line is the one that is coming up highlighted in yellow, which I'm assuming is the problem, just not sure how to fix it.


Here is the code

Private Sub CommandButton1_Click()

a = Worksheets("This week").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If Worksheets("this week").Cells(i, 25).Value = "New" Then

Worksheets("This Week").Rows(i).Copy
Worksheets("New").Activate
b = Worksheets("New").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("New").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("This Week").Activate

End If


Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("This Week").cell(1, 1).Select


End Sub




Thanks for all the help.
Go to the top of the page
 
DanielPineault
post Aug 19 2019, 11:34 AM
Post#2


UtterAccess VIP
Posts: 6,838
Joined: 30-June 11



I'm assuming at the time of running the code you don't have "This Week" Activated and thus you can't select cells within it. Try adding
CODE
ThisWorkbook.Worksheets("This Week").Activate

prior to the problematic line.

Also, small typo, it's Cells() not Cell().

Don't forget Dimming your variables, adding Option Explicit to the top of all your modules and including proper error handling in all your procedures. So try
CODE
Private Sub CommandButton1_Click()
    Dim a                     As Long
    Dim b                     As Long
    Dim i                     As Long

    On Error GoTo Error_Handler

    a = Worksheets("This week").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a
        If Worksheets("this week").Cells(i, 25).Value = "New" Then
            Worksheets("This Week").Rows(i).Copy
            Worksheets("New").Activate
            b = Worksheets("New").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("New").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            'Worksheets("This Week").Activate
        End If
    Next
    Application.CutCopyMode = False

    Worksheets("This Week").Activate 'Put your activate here, prior to the Select otherwise it may not run in the loop/if statement
    Worksheets("This Week").Cells(1, 1).Select

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CommandButton1_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
PaulBrand
post Aug 19 2019, 11:40 AM
Post#3



Posts: 1,719
Joined: 4-September 02
From: Oxford UK


Just a typo in Cells...

ThisWorkbook.Worksheets("This Week").Cells(1, 1).Select
This post has been edited by PaulBrand: Aug 19 2019, 11:42 AM

--------------------
Paul
Go to the top of the page
 
Sotolin
post Aug 19 2019, 11:49 AM
Post#4



Posts: 89
Joined: 27-August 03



THANK YOU BOTH!!!!!!

That worked perfectly... I did not see the Cell() typo.
This post has been edited by Sotolin: Aug 19 2019, 11:50 AM
Go to the top of the page
 
DanielPineault
post Aug 19 2019, 11:52 AM
Post#5


UtterAccess VIP
Posts: 6,838
Joined: 30-June 11



Glad I could help. thumbup.gif

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 05:53 AM