Getting last value in Excel workb and copy [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello, wondering if some1 can help me.

I have 2 worksheets CertNo.xls and UncertB.xls.
In UncertB.xls i have a button. When i click this button i need to get info from the CertNo.xls worksheet.
In this worksheet i have a column A containing numbers which represent a certificate no. the customer column B and the date column C. I need to find the last cert no used and pass it to the UncertB sheet. In this sheet the macro must add a 1 to the number it got to create a new certificate no. and this value goes to cell D5 in UncertB workbook.

Is this possible?

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
copy paste this macro in a standard module . the macro is named "test"
assign this macro to the button

Sub test()
Dim r As Range, x
With Worksheets("certno")
Set r = .Range("A1").End(xlDown)
x = r.Value
x = x & 1
With Worksheets("uncertb")
.Range("D5") = x
End With
End With
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
Sorry I forgot to add
worksheets names need not have ".xls". only workbooks need that suffix
Hey Thanks for the reply.
What would i have to do if the 2 sheets where in 2 differnt workbooks. e.g. UncertB.xls and CertNo.xls and i need to get the info from CertNo and add a 1 to it and paste it in UncertB.xls?

Hope u could help me.
Thanks
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
keep both the saved workbooks open and try this macro( I have not tested)

Sub test()
Dim r As Range, x
With Workbooks("certno.xls").Worksheets("certno")
Set r = .Range("A1").End(xlDown)
x = r.Value
x = x & 1
With Workbooks("uncertb.xls").Worksheets("uncertb")
.Range("D5") = x
End With
End With
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!