macro to generate next number

  • Follow


Hi, i need a macro to look in sheet 2 column A and look at the last filled 
cell. In the  last filled cell i have a number. When i run macro, i need to 
generate (in sheet1 A1) the number from the last filled cell +1.

EX:
sheet 2 last filled cell = 29
sheet1 after macro i need to have in A1 = 30

Can this be done?
Thanks!
0
Reply Utf 3/9/2010 8:58:01 PM

Hi,

Yuo can have a macro if you want but you don't need one, try this

=OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0)
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"puiuluipui" wrote:

> Hi, i need a macro to look in sheet 2 column A and look at the last filled 
> cell. In the  last filled cell i have a number. When i run macro, i need to 
> generate (in sheet1 A1) the number from the last filled cell +1.
> 
> EX:
> sheet 2 last filled cell = 29
> sheet1 after macro i need to have in A1 = 30
> 
> Can this be done?
> Thanks!
0
Reply Utf 3/9/2010 9:08:01 PM

Hi Mike. I really need a macro.
I am using some macros and i need to use this macro along with the other ones.
But i really don't know how to make this work.
Can you help me with a macro?
Thanks!


"Mike H" wrote:

> Hi,
> 
> Yuo can have a macro if you want but you don't need one, try this
> 
> =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0)
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "puiuluipui" wrote:
> 
> > Hi, i need a macro to look in sheet 2 column A and look at the last filled 
> > cell. In the  last filled cell i have a number. When i run macro, i need to 
> > generate (in sheet1 A1) the number from the last filled cell +1.
> > 
> > EX:
> > sheet 2 last filled cell = 29
> > sheet1 after macro i need to have in A1 = 30
> > 
> > Can this be done?
> > Thanks!
0
Reply Utf 3/9/2010 9:17:01 PM

A macro it is then

Sub Sonic()
Dim LastRow As Long
Set SrcSht = Sheets("Sheet2")
Set DstSht = Sheets("Sheet1")
LastRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow)
End Sub

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"puiuluipui" wrote:

> Hi Mike. I really need a macro.
> I am using some macros and i need to use this macro along with the other ones.
> But i really don't know how to make this work.
> Can you help me with a macro?
> Thanks!
> 
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > Yuo can have a macro if you want but you don't need one, try this
> > 
> > =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0)
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "puiuluipui" wrote:
> > 
> > > Hi, i need a macro to look in sheet 2 column A and look at the last filled 
> > > cell. In the  last filled cell i have a number. When i run macro, i need to 
> > > generate (in sheet1 A1) the number from the last filled cell +1.
> > > 
> > > EX:
> > > sheet 2 last filled cell = 29
> > > sheet1 after macro i need to have in A1 = 30
> > > 
> > > Can this be done?
> > > Thanks!
0
Reply Utf 3/9/2010 9:26:01 PM

On Tue, 9 Mar 2010 13:26:01 -0800, Mike H
<MikeH@discussions.microsoft.com> wrote:

>A macro it is then
>
>Sub Sonic()
>Dim LastRow As Long
>Set SrcSht = Sheets("Sheet2")
>Set DstSht = Sheets("Sheet1")
>LastRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
>DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow)
>End Sub

+1 should be added at the end of the last statement like this:

DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow) + 1

Lars-�ke
0
Reply Lars 3/9/2010 9:41:20 PM

Hi Mike, your first formula gives me an error, and the macro retrieves the 
last number. I need to display last number +1.
Am i doing something wrong?
Thanks!

"Mike H" wrote:

> A macro it is then
> 
> Sub Sonic()
> Dim LastRow As Long
> Set SrcSht = Sheets("Sheet2")
> Set DstSht = Sheets("Sheet1")
> LastRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow)
> End Sub
> 
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "puiuluipui" wrote:
> 
> > Hi Mike. I really need a macro.
> > I am using some macros and i need to use this macro along with the other ones.
> > But i really don't know how to make this work.
> > Can you help me with a macro?
> > Thanks!
> > 
> > 
> > "Mike H" wrote:
> > 
> > > Hi,
> > > 
> > > Yuo can have a macro if you want but you don't need one, try this
> > > 
> > > =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0)
> > > -- 
> > > Mike
> > > 
> > > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > > introduces the fewest assumptions while still sufficiently answering the 
> > > question.
> > > 
> > > 
> > > "puiuluipui" wrote:
> > > 
> > > > Hi, i need a macro to look in sheet 2 column A and look at the last filled 
> > > > cell. In the  last filled cell i have a number. When i run macro, i need to 
> > > > generate (in sheet1 A1) the number from the last filled cell +1.
> > > > 
> > > > EX:
> > > > sheet 2 last filled cell = 29
> > > > sheet1 after macro i need to have in A1 = 30
> > > > 
> > > > Can this be done?
> > > > Thanks!
0
Reply Utf 3/9/2010 9:50:01 PM

It's working.
Thanks!

"puiuluipui" wrote:

> Hi, i need a macro to look in sheet 2 column A and look at the last filled 
> cell. In the  last filled cell i have a number. When i run macro, i need to 
> generate (in sheet1 A1) the number from the last filled cell +1.
> 
> EX:
> sheet 2 last filled cell = 29
> sheet1 after macro i need to have in A1 = 30
> 
> Can this be done?
> Thanks!
0
Reply Utf 3/9/2010 10:30:01 PM

6 Replies
1115 Views

(page loaded in 0.09 seconds)


Reply: