Automating going from tab to tab, in the same cell
Posted by Joan L. on September 28, 2001 10:03 AM
I have a file with about 20 worksheet tabs, each one for a different country. Each layout is identical, with different data put in. When I change a forecast #, I need to go to the identical cell address on each tab. Is there a way (perhaps a macro) to move quickly from tab to tab (has to be NEXT, i.e., relative movement), staying on the same cell, of course? That is, I would put in the new # in e.g., A100 on the first tab, do the automation and be taken to A100 on the next tab, do the same automation to get to the next tab after I put in the data, cycling through all tabs? THANKS
Posted by Barrie Davidson on September 28, 2001 10:24 AM
Joan, are you putting the same information in A100 in every sheet?
BarrieBarrie Davidson
Posted by Joan L. on September 28, 2001 10:36 AM
In answer to your question--no. (Darn.) The data comes in to me with the forecast for each item, by country in local currency. What the file does is convert it to US $ and total it. So, what I have to do is go to each tab in the work sheet, over and over again, by product. Each product is listed in a different row. So what I would like to do is take the hard copy sheet I have for each product, and type in the # quickly. What slows me down is clicking on the tab with my mouse and then scrolling over and around to find the right cell each time. It would be great if I could read a # off my hard copy, do a key combo assigned to a macro that would go to the next tab in the identical place. Wow, would that be fast. That's why I would like a macro that says "go to the next tab--whatever the next one is--and go to the same cell I am in on this tab, in that tab--whatever that cell is! Then I can type the next # from my hard copy. I hope that's clearer and thanks for offering to help!
Posted by Barrie Davidson on September 28, 2001 10:42 AM
Joan, if I understand your situation, this code will do what you need.
Sub Next_Worksheet()
' Written by Barrie Davidson
Dim currentAddress As String
currentAddress = ActiveCell.Address
ActiveSheet.Next.Activate
Range(currentAddress).Select
End Sub
Regards,
BarrieBarrie Davidson
Posted by Joan L. on September 28, 2001 11:17 AM
Thanks, Barrie! I will try it on Monday (my first chance and right before we start the quarterly process) and let you know how it goes! You Macro Experts are AWESOME!
Posted by Joan L. on September 28, 2001 11:41 AM
Okay...scratch the part of the last message that says we'll try it Monday. The nags here made me try it right now and IT WORKS. One little glitch which isn't too much. When we put the data in and hit return, it goes down one cell. So there has to be a line added in the Macro that moves the address back up one row. (i.e., if I am in cell A100 and put in data then hit enter, it is now in A101. I need to go to A100 in the next worksheet, not A101 which is where the macro will take me, unless I manually move up a cell first.) As you can see, we are pretty much Macro know-nothings. This is probably an easy thing to add. THANKS SO MUCH. MY LIFE IS GOING TO BE BOW-COO EASIER!
Posted by Juan Pablo on September 28, 2001 11:43 AM
Put this instead then:
Sub Next_Worksheet()
' Written by Barrie Davidson
Dim currentAddress As String
currentAddress = ActiveCell.Offset(-1,0).Address
ActiveSheet.Next.Activate
Range(currentAddress).Select
End Sub
Juan Pablo
Posted by Joan L. on September 28, 2001 12:29 PM
Bravo! Plus, I am beginning to follow the logic, so maybe there is hope that I will be able to write Macros myself...other than the little ones I record with Excel's help. You guys are the best!
