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