How do I post a stored range?

[Gaark]

Hello all!

I've been hunting around looking for a way to update my file and I need a bit of extra help please.

My file has 250+ sheets in it and the first 2 are the main index and the calculation information base, the rest of the sheets are of individual products which have their own values for pricing purposes. I have plans to upgrade the machinery I am working with which means I need to upgrade my pricing mechanism to accurately show what my costs will be based on the new machinery.

OK, the first 4 columns of each workings sheet are fixed and do not need changing.
12 more columns need inserting before column E
my named range needs to be pasted into the gap, and replace the existing 3 columns of pricing calculations.
Any data outside of these columns shouldn't be harmed except for T4 which has a little update.

This is what I have so far, but bugs out when it reaches the paste function because I don't know how to get it to paste "data":

Sub Macro6()
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Dim sht As Worksheet
Dim data As range
Set data = range("3!E1:R29")


For Each sht In ActiveWorkbook.Worksheets
    Columns("E:O").Select
    range(Selection, Selection.Offset(0, 0)).Insert shift:=xlToRight
    range("e:s").Select
    ActiveSheet.Paste
    range("t4").Select
    ActiveCell.FormulaR1C1 = "=RC[-18]/9"
    Next sht
End Sub

Thanks for reading

 

[JLGWhiz]

See if this will work:

Sub Macro6()
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Dim sht As Worksheet
Dim data As Range
Set data = Range("3!E1:R29")
    For Each sht In ActiveWorkbook.Worksheets
        Columns("E:O").Select
        Range(Selection, Selection.Offset(0, 0)).Insert shift:=xlToRight
        data.Copy ActuveSheet.Range("E1")
        Range("t4").Select
        ActiveCell.FormulaR1C1 = "=RC[-18]/9"
    Next sht
End Sub

 

[Michael M]

maybe this

Sub Macro6()
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Dim sht As Worksheet
Dim myrange As Range
Set myrange = Sheets("3").Range("E1:R29")


For Each sht In ActiveWorkbook.Worksheets
    With sht
    .Columns("E:O").Insert
    myrange.Copy sht.Range("E1")
    .Range("t4").FormulaR1C1 = "=RC[-18]/9"
    End With
    Next sht
End Sub

 

[Gaark]

Thanks JLG, the data range got pasted right where I wanted it.

One glitch that did pop up though, the sheet didn't increment and the macro magic on sheet "6" went through the 'insert, paste' loop a lot and I ended up with a sheet that went into column AXO and more LOL.

I've gone exploring and found what I needed to get the macro to do all that was needed, it involved making a new sheet that stored my source data.

This is what I ended up with:

Sub Macro6()
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Dim sht As Integer
Dim data As range
sht = 4
Set data = range("SOURCE!h1:R29")


Do While sht < 10
    Sheets(sht).Select
        Columns("h:r").Select
        range(Selection, Selection.Offset(0, 0)).Insert shift:=xlToRight
        data.Copy ActiveSheet.range("h1")
        range("t4").Select
        ActiveCell.FormulaR1C1 = "=RC[-18]/9"
    sht = sht + 1
Loop
End Sub

 

[Gaark]

Thanks Michael

(I'll be adjusting the " While sht < 10 " to 260 or so)

 

[Michael M]

Great, but get rid of the Select.Selection lines

Sub Macro6()
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Dim sht As Integer
Dim data As range
sht = 4
Set data = range("SOURCE!h1:R29")
Do While sht < 10
    Sheets(sht).Columns("h:r").Insert
        data.Copy ActiveSheet.range("h1")
        range("t4").FormulaR1C1 = "=RC[-18]/9"
    sht = sht + 1
Loop
End Sub