macro to copy and paste a range to multiple ws in workbook

[cagey414]

trying to debug a former teammates macro (novice user myself). object is to copy a small range, formatting and all and post in the same location in multiple worksheets that match certain criteria. in this case, moving July end of month (range g6:k8) to daily breakdown worksheets (all of July sheets (07-02->08-02) in the 365 tabs workbook).

N1 is 07-02, n9 is 08-01
tabs are named 7-2, 7-3, 7-4, etc.

Sub entiremonth()
Dim x As Workbook
Dim y As Workbook
Set x = ActiveWorkbook
Dim istart As Integer
Dim iend As Integer
Dim i As Integer



starttab = Range("N1")
endtab = Range("N9")


Set y = Workbooks.Open("J:test.xlsm")
Set y = ActiveWorkbook

y.Activate

i = endtab - starttab


For Each Worksheet In i
     ws.Activate
    x.Activate
    Range("G6:g8*h6:h8*i6:i8*k6:K8").Select
    Selection.Copy
    y.Activate
    Range("J23").Select
    
Selection.pastespecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next Worksheet


End Sub

any help is appreciated!

 

[My Aswer Is This]

I do not see how this Macro you provided could have been doing what you want.

Now if you want to put the value "10" in some particular cell I can write you a script that will copy the Range("G6:K8") from a particular sheet and paste into the sheet with a name of 10- whatever like 10-1, 10-2

I need to know where to find the value with the sheet name to start with and what is the name of the sheet with the data to copy.

Give me some details and I may be able to help you. The script you have here does not help me at all.

 

[cagey414]

good so its not just me who can't figure out what my colleague was doing....

N7 has the '7' for the month.

the sheet names would be in a different workbook, each sheet labeled by date: 7-1, 7-2, 7-3 etc.

I saw a solution for how this needs to work based on color coded tabs that I might try. Your help is greatly appreciated.

 

[My Aswer Is This]

If you decide you need help from us please check back in and please explain what you mean by sheet names will be another workbook.

No need me working on this when you are considering other ways of doing this you have found some place else.

 

[cagey414]

so one workbook has the data entry sheet plus a few other sheets to do various things.
the second workbook has the daily forecast sheets (all 365 of them) for the next fiscal year.

heres the code that i modified and got to work (test has replacced the actual file name)

Sub entiremonth_K()
Dim x As Workbook
Dim y As Workbook
Set x = ActiveWorkbook
Dim ws As Worksheet


 
Set y = Workbooks.Open("test.xlsm")
Set y = ActiveWorkbook


    For Each ws In Worksheets
        If ws.Tab.Color = 255 Then
        ws.Activate
        x.Activate
    Range("G6:K8").Select
    Selection.Copy
    y.Activate
    Range("J23").Select
    
Selection.pastespecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


        Else
        End If
    Next ws
    
     
End Sub

thanks for all your efforts, if it wasn't for the great ideas on this website, i would have never have figured it out.