Re: [ExcelVBA] Re: Macro to copy data from one cell and add to next empty cell in another worksheet

 

Thankyou, it *was* the split line that caused it, all working now

regards

Tony

On 19 July 2011 16:09, David Grugeon <yahoo@grugeon.com.au> wrote:

> **
>
>
> Hi Tony
>
> It doesn't say
>
> dim nrows=
>
> It says
>
> dim nrows
> nrows=
>
> Application.WorksheetFunction.CountA(Sheets("Data").Range("A1:A65000")
> )
>
> Dim nrows should be on one line
> The following rows of the above text should all be on the next line. (from
> nrows= to the closing bracket) They got split into three in the email.
>
> Best Regards
> David Grugeon
> Excel VBA Group Moderator
>
> -----Original Message-----
> From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
> Of Tony
> Sent: Tuesday, 19 July 2011 9:51 PM
> To: ExcelVBA@yahoogroups.com
> Subject: [ExcelVBA] Re: Macro to copy data from one cell and add to next
>
> empty cell in another worksheet
>
> Hi,
>
> I know this is an old message but I went to use this code today and when I
> typed
>
> <snip>
> in Option Explicit
> > Function Add_Data(NewData)
> > Dim nRows=
> </snip> Here I got an error message:"Compile Error: Expected: Expression"
> NRows = stayed highighted in red even when I typed in the rest of the code.
> Thinking I had made an error I copied and pasted the code from the message,
> same problem, so I retyped and still the same.
>
> Has anything changed in Excel 2007 to cause this?
>
> Regards
>
> Tony
>
> --- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...>
> wrote:
> >
> > Sure, simple enough to do.
> > In fact, you can put the macro call in a sheet_change event and then
> > it will 'automatically'
> > add it whenever you change the cell contents!
> >
> > In a module, I created:
> >
> > Option Explicit
> > Function Add_Data(NewData)
> > Dim nRows
> > nRows =
> > Application.WorksheetFunction.CountA(Sheets("Data").Range("A1:A65000")
> > )
> > Sheets("Data").Cells(nRows + 1, 1) = Now()
> > Sheets("Data").Cells(nRows + 1, 2) = NewData End Function
> >
> > (It assumes that there is ALWAYS a record in column "A")
> >
> > then, in the Sheet module, I created a Change Event:
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim stat
> > If ((Target.Address = "$C$3")) Then
> > If (Target.Value <> "") Then _
> > stat = Add_Data(Target.Value)
> > Range("C3").Select
> > End If
> > End Sub
> >
> >
> > (it assumes that you're making the entries in cell C3.
> >
> > Paul
> >
> >
> >
> >
> >
> > ________________________________
> > From: Tony <studiot@...>
> > To: ExcelVBA@yahoogroups.com
> > Sent: Monday, October 12, 2009 4:34:21 AM
> > Subject: [ExcelVBA] Macro to copy data from one cell and add to next
> > empty cell in another worksheet
> >
> >
> > Hi all
> >
> > I would like to be able to enter data in a specific cell (c15) in a
> worksheet named "Daily Data", and then have run a macro that would take
> that
> cell entry and add it to the next empty row in column B in a worksheet
> named
> "Data"
> >
> > Any ideas gratefully received
> >
> > Thanks in advance for your help
> >
> > regards
> >
> > Tony
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> ------------------------------------
>
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the
> ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>

--
Save paper - do you really need to print this email?
Save electricity - do you really need to send this email?
Save your own energy - do you really need to read this email?

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (9)

Recent Activity:

• New Members 9

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___