code optimization

[piernik]

Hello., could you help me optimized my below code?
I want to copy a few data from one sheet to other and then print them.
How can I print active cell's? (the scope is changing)

Range("e5").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,2,0)"
Selection.AutoFill Destination:=Range("E5:E303")
Range("E5:E303").Select

Range("I5").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,3,0)"
Selection.AutoFill Destination:=Range("I5:I303")
Range("I5:I303").Select

Range("M5").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,4,0)"
Selection.AutoFill Destination:=Range("M5:M303")
Range("M5:M303").Select

Range("B1").Select
ActiveCell.FormulaR1C1 = "=PLAN!R[1]C[42]"
Range("B2").Select

Range("A1").Select
ActiveSheet.Range("$A$4:$Y$303").AutoFilter Field:=25, Criteria1:=">0"
Columns("Y:Y").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select

Any help on getting this to work would be most appreciated!

[SamT]

I will do one for you. The rest are the same

Range("e5").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,2,0)"
    Selection.AutoFill Destination:=Range("E5:E303")
    Range("E5:E303").Select

With Range("e5")
    .FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,2,0)"
    .AutoFill Destination:=Range("E5:E303")
    End With

[mana]

Sub test()


    Range("E5:E303").Formula = "=VLOOKUP(C5&A5,plan_zmianowy!B:C,2,0)"
    Range("I5:I303").Formula = "=******"
    Range("M5:M303").Formula = "=******"


    Range("B1").Formula = "=PLAN!AQ1"
    
    Range("A4").Resize(300, 25).AutoFilter Field:=25, Criteria1:=">0"
    
    Columns("Y").Hidden = True


End Sub

[piernik]

that means that I should used the same code for others range.


It's clear for me.


But how can I print selection when the scope is changing for example some times I have 5 kolumns and 20 row but another time I have 5 columns and 35 rows.

[piernik]

I don't understand why you used

Range("B1").Formula = "=PLAN!AQ1"

[p45cal]

You're not saying what you want to print so this is a guess (Preview set to True to save paper)

Range("E5:E303").FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,2,0)"
Range("I5:I303").FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,3,0)"
Range("M5:M303").FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,4,0)"
Range("B1").FormulaR1C1 = "=PLAN!R[1]C[42]"

ActiveSheet.Range("$A$4:$Y$303").AutoFilter Field:=25, Criteria1:=">0"
Columns("Y:Y").EntireColumn.Hidden = True
With ActiveSheet.PageSetup
  .FitToPagesWide = 1
  .FitToPagesTall = 1
End With
'Range("A1:X303").Select  'debug line
'PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)
Range("A1:X303").PrintOut Copies:=1, Preview:=True, Collate:=True  ', IgnorePrintAreas:=False

[piernik]

I want to print new copy range.
Print must be on one page.
range must change depending on the number of rows.
The number of columns remains unchanged.

[p45cal]

Well, that's pretty close to what I've suggested - one page for everything, but still I have no idea where/what new copy range is.

[piernik]

This will be a new sheet to which I will paste data from the above ranges

[SamT]

WorkSheets("Sheet1").Printout

[p45cal]

the above ranges

No idea what those ranges are so a complete guess:

Sub dghyrw()
Range("E5:E303").FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,2,0)"
Range("I5:I303").FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,3,0)"
Range("M5:M303").FormulaR1C1 = "=VLOOKUP(RC3&RC1,plan_zmianowy!C2:C5,4,0)"
Range("B1").FormulaR1C1 = "=PLAN!R[1]C[42]"

ActiveSheet.Range("$A$4:$Y$303").AutoFilter Field:=25, Criteria1:=">0"
Columns("Y:Y").EntireColumn.Hidden = True
Set CopySource = Intersect(ActiveSheet.AutoFilter.Range, Range("E:E,I:I,M:M"))
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
CopySource.Copy NewSht.Cells(1)
With NewSht.PageSetup
  .FitToPagesWide = 1
  .FitToPagesTall = 1
End With
NewSht.UsedRange.PrintOut Copies:=1, Preview:=True, Collate:=True
End Sub