Macro to go to same cell next worksheet

[AntBlabby8]

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:

 

[erik.van.geit]

Antblabby8,

Could you use this ?

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]

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]

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]

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]

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.