Macro to go to same cell next worksheet

AntBlabby8

Board Regular
Joined
Apr 18, 2002
Messages
190
I often have 10-25 worksheets in a workbook, all with the same layout, that need to have specific data entered at the same cell address. For example, a row might be called "Utilities" in column A and there would be 12 more columns, one for each month of the year. Each worksheet would be a different division of our company and after month-end, I need to input the actual charges for the month for utilities for each division. I have to go to the same cell in each worksheet, to enter that division's utilities bill. I'd like to create a macro with a keyboard shortcut that I can run after I put the data in the first worksheet, that will take me to the same cell on the next worksheet and so on, to speed up the process. I do not know much about Visual Basic and I can't seem to get it to work through recording a macro! Any help? THANKS AS USUAL :wink:
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Antblabby8,

Could you use this ?
Code:
Sub put_info_in_all_sheets_same_cell()
Dim sh As Object
For Each sh In Sheets
sh.Select
Range("A1") = InputBox("Put your data here", "DATA", Range("A1"))
Next sh
End Sub

kind reagrds,
Erik
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Or this will just take you to the cell...

Sub NextSheet()

Dim iSheet As Integer
Dim sAddress As String

sAddress = ActiveCell.Address
iSheet = IIf(ActiveSheet.Index <> Worksheets.Count, ActiveSheet.Index + 1, 1)
Worksheets(iSheet).Activate
ActiveSheet.Range(sAddress).Activate

End Sub
 

vinman

New Member
Joined
Jan 27, 2003
Messages
19
I don't think you need a macro. Try this: before you start entering data, select all of your sheets and click on the cell you want to enter the data in. Then deselect all of the sheets. You can then begin to enter your data on the first sheet after you are done use the ctl+Pageup or Ctl+pagedown keys to scroll through the tabs. They should all be set at the same cell.

Vinman
 

AntBlabby8

Board Regular
Joined
Apr 18, 2002
Messages
190
Craig's solution worked...I tried the first and I don't think it addressed what I needed. Probably because I didn't explain it clearly. But thanks all! I really should learn Visual Basic but I think it's too much for my lazy brain! I'll always depend on you all instead. :)
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
I'd consider vinman's solution too though. It's good to avoid vba for such things if possible. It is the kind of solution you can easily use in any workbook without bothering with the code.
 

Forum statistics

Threads
1,147,668
Messages
5,742,514
Members
423,734
Latest member
123hmMission

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
Top