Copy selected cells to multiple worhsheets

[timecmdr]

Hi Guys,

Need help with a simple query. I'm trying to create a macro that will copy the selected cells to an X number of worksheets proceding the current one but the data has to be in the same location.

Can anyone help please.

[mudraker]

How is X (number of sheets) calculated?
When you say sheets preceding the current one do you mean as you see the Tabs lined up from left to right or do you mean in somesort of name format?

[timecmdr]

Thanks for your reply.

I have a woorkbook with a sheet for each week. What I want to be able to do is select 2 cells and click a button that will ask how many weeks to copy these cells to.

I could then enter, say "10" and the cells would then be copied to the ten proceeding weeks. Assuming that the sheets are in order!

[mudraker]

Try this macro
It will only run if 2 cells are selected. The 2 cells do not have to be next to each other. e.g cells could be a3 & d8

It tells you the maximum number of weeks that can be entered - this is the sheet count to the left of the activesheet.
If invalid info entered into inbox it exits macro

selected cell value copied to same cell on other sheets

Sub CopyData()
   Dim sResp As String
   
   Dim iFor As Integer
   Dim iIndex As Integer
   
   Dim Rng As Range
   
   If Selection.Count = 2 Then
      Stop
      iIndex = ActiveSheet.Index
      sResp = InputBox("Copy For How Many Weeks?" _
      & Chr(10) & Chr(10) _
      & "Maximum number of weeks is " & iIndex - 1)
      If sResp = vbNullString Or Not IsNumeric(sResp) Then
         Exit Sub
      ElseIf Val(sResp) > iIndex - 1 Then
         MsgBox "Too Many Weeks Entered!", vbInformation
         Exit Sub
      End If
   End If
   
   For iFor = iFor To iIndex - 1 Step 1
      For Each Rng In Selection
         Sheets(iFor).Range(Rng.Address).Value = Rng.Value
      Next Rng
   Next iFor
End Sub

[timecmdr]

Mudraker you star this works brilliantly.

Thanks a Million!!!!

[mudraker]

Glad to hear I hit the nail on the head with the macro