"Capture" entry for use in another sheet

Von Pookie · Aug 8, 2003

Let me see if I can explain this.

I have 1 worksheet that I add information to weekly. After I do that, there is another worksheet that I open and enter the same numbers that I just put in the other sheet, along with some other information.

While I enter an entire row’s worth of information in the first sheet, I only need the numbers of that particular item for the other. Is there some way I could “capture” what numbers were entered into column A each time, then have that information automatically entered onto the second sheet? Or maybe pop up in a form or something so they can be “ok’d” before entered into the cells (just thinking on the fly, here).

The numbers in column A are always in the format of DS1234—2 letters and 4 numbers (however the letter ‘D’ will change each year to ‘E,’ ‘F’ and so on)

This is the tracking form where the numbers are entered:

Register.xls

A

B

C

D

4

DATE SENT

NUMBER

SENT BY/CAR

REMARKS

5

6

Sheet1

The numbers should simply be one to each cell, starting from B5.

The rest of the information I can write a macro on my own to do what I want (go me!)

If anyone has any ideas, I’m interested! And bored (which is why I ended up thinking of this in the first place)! ENTERTAIN MEEEEE!

Thanks, guys

tusharm · Aug 8, 2003

You could use a SheetChange event macro to track relevant changes in the first worksheet that would update the 2nd worksheet as below. Note that I used a Workbook level event rather than a worksheet event.

Code:

Option Explicit
Option Compare Text

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim aCell As Range
    If Not (Sh.Name = "FirstWorksheet") Then Exit Sub
    On Error GoTo errXIT
    Application.EnableEvents = False
    For Each aCell In Target.Cells
        If aCell.Column = Columns("a").Column Then
            ThisWorkbook.Worksheets("SecondWorksheet") _
                .Cells(Rows.Count, Columns("b").Column).End(xlUp) _
                .Offset(1, 0).Value = aCell.Value
            End If
        Next aCell
    Application.EnableEvents = True
    Exit Sub
errXIT:
    MsgBox "Update of Secondworksheet may not be complete." & vbNewLine _
        & "Error encountered: " & Err.Description & " (" & Err.Number & ")"
    Application.EnableEvents = True
    End Sub

Yogi Anand · Aug 8, 2003

Hi Kristy:

I may not have gotten the whole picture of what is happening with the two worksheets -- however, have you considered creating links in the second worksheet to the appropriate cells in the first worksheet -- so

when an entry of interest is made in the first worksheet, it shows up in the second worksheet because of cell_linking ...

anyway, please do tell if something like this will work for you -- or have I missed something in what you are intending to accomplish.

Von Pookie · Aug 9, 2003

For starters, I won't be able to try your option until Monday, Tusharm. Just so you know

Yogi: I don't think that will really work.The second sheet (the teensy sample in the OP) is used weekly as-is. I don't save it each time with the different information--I keep it blank so I can just enter the information every week.

Von Pookie · Aug 11, 2003

Ugh. Back to work today. But this also means I can actually take a look at the code here.

Tusharm, I think either you didn't quite understand what I was saying, or I managed to explain it incorrectly.

I'm not transferring the information from one sheet to another, it's supposed to be from one workbook to another.

(I just read my OP again. I did say "worksheet." Sorry, my fault)

I tried, but I can't figure out how I could change the code from 'sheets to 'books.

tusharm · Aug 11, 2003

Replace

Code:

            ThisWorkbook.Worksheets("SecondWorksheet") _

with

Code:

            Workbooks("OtherWorkbook").Worksheets("SecondWorksheet") _

earlyd · Aug 11, 2003

Hi - this might help. When you get into transferring data from one book to another, it gets tiring to always type in Workbooks("ThisBook.xls").sheets("MySheet").cells(a,b).value = Workbooks("ThatBook.xls").sheets("MySheet").cells(a,b).value, so..... First use a couple of set statements like Set SourceSht = Workbooks("ThatBook.xls").sheets("MySheet") and DestSht = Workbooks("ThisBook.xls").sheets("MySheet"). Then you can just use DestSht.cells(a,b) = SourceSht.cells(a,b)

Hope this helps

Von Pookie · Aug 11, 2003

Hey, that seems to be working.

I took out the If Not bit, as I didn't think it would work with how I've got the first workbook set up.

It seems to be doing the trick, though.

Thanks

Now I'm off to set up the rest of it (which is what I've been assuming I can do myself...we'll see

)

tusharm · Aug 11, 2003

Von Pookie said:
Hey, that seems to be working.

{snip}

What did you expect?

You come across as surprised.

I think that if I think hard enough I will definitely think that I should be offended by that lack of faith. {vbg}

Von Pookie · Aug 11, 2003

Oh, come off it, you

It wasn't that I was surprised it worked (though I was a little after I took that bit out myself)...I was just worried it wouldn't do what I was looking for. As in I didn't explain it well enough or something.

I'm currently working on my other ideas for it: maybe instead of putting them in the other book each time the sheet changes, have it 'capture' them and then bring them up in a box or something to let me check the numbers first.

Hm...wonder if I can do that myself. Back to Excel, then. Which I've just been staring at for a while now.

"Capture" entry for use in another sheet

Von Pookie

MrExcel MVP

tusharm

MrExcel MVP

Yogi Anand

MrExcel MVP

Von Pookie

MrExcel MVP

Von Pookie

MrExcel MVP

tusharm

MrExcel MVP

earlyd

Well-known Member

Von Pookie

MrExcel MVP

tusharm

MrExcel MVP

Von Pookie

MrExcel MVP

Similar threads

Share this page

"Capture" entry for use in another sheet

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock