Macro with Range of Worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is probably simple to do, but I don't do this enough to know.

How would I edit this macro to paste to a range of 134 worksheets that are
after two that I do not want to paste to. In this example I have gone to
each worksheet and pasted one at a time.

Range("G14").Select
Selection.Copy
Sheets("2").Select
Range("G14").Select
ActiveSheet.Paste
Sheets("3").Select
Range("G14").Select
ActiveSheet.Paste
Sheets("4").Select
Range("G14").Select
ActiveSheet.Paste
 
try this idea

Sub copytosheets()
For i = 3 To Sheets.Count
Sheets(i).Range("a1") = Range("a1")
Next
End Sub
 
One way:

If you're trying to copy from sheet "1" to Sheet "4" and subsequent
sheets, and they're in order:

Public Sub CopySkipPaste()
Dim rCopy As Range
Dim i As Long
Set rCopy = Sheets("1").Range("G14")
For i = 4 to Worksheets.Count
rCopy.Copy Destination:=Sheets(i).Range("G14")
Next i
End Sub
 
Bingo. Thank you

JE McGimpsey said:
One way:

If you're trying to copy from sheet "1" to Sheet "4" and subsequent
sheets, and they're in order:

Public Sub CopySkipPaste()
Dim rCopy As Range
Dim i As Long
Set rCopy = Sheets("1").Range("G14")
For i = 4 to Worksheets.Count
rCopy.Copy Destination:=Sheets(i).Range("G14")
Next i
End Sub
Click to expand...
 
Sub selectsheetindex()
'Sheets(1).Select 'gets 1st in line
Sheet1.Select 'gets index number
End Sub


The macro must be put in a regular module vs a sheet module. It WILL take
the value of whatever is in cell a1 of the sheet from where executed (sheet
1 or 2 would be nice) and place in all but the 1st two sheets based on where
they are in the workbook. The 1st 2 tabs. So, it assumed you would have
11111 in cell a1 of sheet 1 and execute the macro from that sheet. Now that
sheet and the next one will not be changed but all the others will.

Send your email to my email and I will send you a sample workbook where it
works.
 
Back
Top