getting last value in Excel workb and copy Closed

Question

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?

venkat1926 · Answer

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

venkat1926 · Answer

Sorry I forgot to add
worksheets names need not have ".xls".  only workbooks need that suffix

venkat1926 · Answer

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

Getting last value in Excel workb and copy

3 responses

Similar discussions

Subscribe To Our Newsletter!