Copying a value in specific cell from active workbook to another

[SarahMH]

Hi
I am familiar with Access VBA and basic VB not really excel vba. I have been tasked with a problem and I am a little stumped - can anyone help?

An object is scanned and the bar coder opens a new workbook(1) and enters the barcode into cell A1. When the next scan occurs it opens a new workbook(2) and puts the bar code into cell A1 - as you can imagine this is not practical.

I need to get the value of cell A1 - when the new workbook is activated, pass the value to a variable then close the active spreadsheet and put the variable into the next empty cell in the data collection spreadsheet.

I am stuck on - when to call the procedure - do I wait until 20 active workbooks are open and loop through or can it be done automatically when a new a1 cell is activated?

I would be grateful for any ideas

Many thanks

 

[jksmi]

Hi, are you using the Excel NewWorkbook event? in which case it might be easier to collect the A1 value and close on each new workbook (you could use the SheetChange event to trigger collecting A1 once you bar coder has added it).

With regards finding the next blank cell, you could use something like this which is quite quick...

With workbooks(2).ActiveSheet
    varCellValue = .Cells(1, 1)
    i = 1
    ' find end of existing data
    Do While varCellValue <> Empty
        i = i + 1
        varCellValue = .Cells(i, 1)
        Loop
    .Cells(i, 1).Select
    .Paste
End With

Hope this helps, Jamie

 

[SarahMH]

Thanks jmski

The second part i totally understand but the first I don't.

I must be thick so please would you clarify where the code goes - in the module or sheet1 of the datacollection sheet?

I have tried in a couple of places and it doesn't work - it isn't activated when a new workbook is opened or when the sheet is changed? and I cannot use the step through - so I know something is wrong. R there any examples you could point me to?

once again many thanks!!

 

[jksmi]

Hi Sarah,

you will have to use a class in order to use application events - might look a little complicated but its v. easy really!

I dont know of any examples so I've cobbled together a quick one...
Insert a new class in a workbook, call it ExcelAppEvents and paste the following code...

[COLOR=green]' declare two types of object that can have events,
' application and workbook[/color]
Public WithEvents app As Application
Public WithEvents wbk As Workbook

Private Sub Class_Initialize()
[COLOR=green]' the class will call this code when it is initialized (i.e. set Xapp = new ExcelAppEvenets)[/color]

Set app = Application       [COLOR=green]' point app to the excel application[/color]
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
[COLOR=green]' once initialized app will listen for any application events,
' below we tell it to take action on the NewWorkBook Event

' tile the window to show an immediate effect[/color]
Application.Windows.Arrange xlArrangeStyleTiled

[COLOR=green]' set wbk so that we can capture sheetchange event[/color]
Set wbk = Wb

Call FixSheetName(wbk)      [COLOR=green]' call some other code for example[/color]
End Sub

Private Sub wbk_SheetChange(ByVal sh As Object, ByVal Target As Range)
[COLOR=green]' once we 've set the wbk var we can capture workbook events

' msg box some info about the change[/color]
MsgBox "Sheet : " & Target.Worksheet.Name & vbCrLf & "Range : " & Target.Address & vbCrLf & "Changed to : " & Target
End Sub

now insert a new module and paste the following code...

Public Xapp As ExcelAppEvents       [COLOR=green]' this var will hold our new class,
' it will listen for events that we have told it to listen out for[/color]

public Sub InitializeApp()
[COLOR=green]' but first we must initialize it[/color]
Set Xapp = New ExcelAppEvents
End Sub

public Sub FixSheetName(ByVal wbk As Workbook)
Dim sh As Worksheet
[COLOR=green]' this bit of code will get called later, from within the class
' just as an example (usually would put class procs in the class!)[/color]

Set sh = wbk.ActiveSheet

[COLOR=green]' enter a value into sheet(1).cell(1,1) or A1,
' this will trigger an event[/color]
sh.Cells(1, 1) = "test enter value"
sh.Name = "test sheet rename"

End Sub

now run initializeApp > create a new workbook > event should be caught

whenever you add a new workbook or make a chenge to the new workbook it will be listening out to action events until you either exit excel or reset you code.

Hope this helps, Jamie

 

[SarahMH]

Thanks a million - it works like a dream.

The datacollection worksheet opens - which activates the code and captures any new workbooks "A1" value(the scanner action), closes that workbook and pastes the value underneath the last active cell. When the datacollection workbook is closed the data sheet is copied and saved to a .csv file with the name the user inputs.

I couldn't have done it without your help!! Thanks again.

 

[jksmi]

no worries... glad to help, Jamie