"Capture" entry for use in another sheet

Von Pookie

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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
ABCD
4DATE SENTNUMBERSENT BY/CARREMARKS
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!
ball.gif



Thanks, guys :)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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) :oops:

I tried, but I can't figure out how I could change the code from 'sheets to 'books.
 
Upvote 0
Replace
Code: 
            ThisWorkbook.Worksheets("SecondWorksheet") _
with
Code: 
            Workbooks("OtherWorkbook").Worksheets("SecondWorksheet") _
 
Upvote 0
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
:)
 
Upvote 0
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 (y)

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 :) )
 
Upvote 0
Von Pookie said:
Hey, that seems to be working.

{snip}
Click to expand...
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}
 
Upvote 0
Oh, come off it, you :p

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Copy Info to Another Sheet
2
Replies
17
Views
655
mumps
mumps
C
  • Question
Use Row Data to Create New Sheet from Template and Reference Data
Replies
0
Views
426
ChrisMas92
C
E
  • Question
From two dates, dividing the years into different columns, create hyperlinks organized by quantity and not by dates
Replies
0
Views
169
ExcelenteNovel
E
X
  • Question
Populate info from one tab using cell as a reference
Replies
1
Views
270
etaf
etaf
G
  • Question
Calling macro via IF statement when range values are present
Replies
2
Views
246
NoSparks
N

Forum statistics

Threads
1,216,058
Messages
6,128,538
Members
449,456
Latest member
SammMcCandless

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top