View Full Version : code optimization
piernik
10-20-2017, 02:54 PM
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!
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
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
10-23-2017, 12:55 PM
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
10-23-2017, 12:57 PM
I don't understand why you used
Range("B1").Formula = "=PLAN!AQ1"
p45cal
10-23-2017, 02:10 PM
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
10-23-2017, 02:29 PM
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
10-23-2017, 02:40 PM
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
10-23-2017, 02:56 PM
This will be a new sheet to which I will paste data from the above ranges
WorkSheets("Sheet1").Printout
p45cal
10-24-2017, 01:53 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.