VBA copy and paste from one workbook into all Workbooks + printing

nayseem

New Member
Joined
Mar 22, 2016
Messages
14
Earlier today I was able to get some help in editing my code for repeating formatting in all workbooks except the first two.

I have two additional asks/needs:
1) I want to copy a range (F6:L11) from the first worksheet into all the following worksheets in cell C18.
2) I want the macro to format and print all the visible worksheets - narrow margins, landscape orientation, fit worksheet to one page and print two copies

Is there a way to get these two things in the code below?

When I run the macro now, I receive the following error "Compile error: Method or data member not found"

Any advice/help would be greatly appreciated it!


Code:
Sub Macro1()
Dim ws As Worksheet
Dim I As Long


    For I = 3 To ThisWorkbook.Sheets.Count
        
        Set ws = ThisWorkbook.Sheets(I)


        With ws
            .Columns("A:O").EntireColumn.AutoFit
            .Columns("M:N").ColumnWidth = 12.71
            .Rows("1:1").EntireRow.AutoFit
            .Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            
            With .Range("A1:O1")
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorLight2
                    .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0
                End With




                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            
            .Range("F1").Value = "Opportunity Report"
            .Range("H1").FormulaR1C1 = "=TODAY()+1"
            
            With .Range("H2:H40")
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                                      Formula1:="=""Due"""
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1).Font
                    .Color = -16383844
                    .TintAndShade = 0
                End With




                With .FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 13551615
                    .TintAndShade = 0
                End With
                .FormatConditions(1).StopIfTrue = False
            End With
    
    'Copy and PasteSpecial
    Worksheets("Instructions").Cells("F6:L11").Copy
    Worksheets().Cells("C18").PasteSpecial
     
          
            
        End With
        
    Next I


End Sub
 

Forum statistics

Threads
1,082,630
Messages
5,366,656
Members
400,909
Latest member
ola97316

Some videos you may like

This Week's Hot Topics

Top