View Full Version : [SOLVED:] VBA macro for print out
BjarneHansen
02-05-2022, 02:12 AM
Hey
i have a ? aboud a macro wher i print out a list from sheet "bogføring" but it print all out
i want it to only print out the select year i sheet "T" C3 if i change year in C3 the print out will change
and the year start in sheet "T" C4 and end in sheet "T" C5
in sheet "bogføring" i only have 2 year now but more will com
sorry my english
arnelgp
02-05-2022, 04:11 AM
i don't have printer so i cannot test.
BjarneHansen
02-05-2022, 05:27 AM
it works but way di it hide all thi other data can i get it so it will show all data after print out:clap:
BjarneHansen
02-05-2022, 05:36 AM
thanks it works
arnelgp
02-05-2022, 06:46 AM
to show all data again replace your code with this:
Private Sub CommandButton1_Click()
'--------------------------------------------------------------------------'
' Printer til sidste række og hopper herefter til sidste celle i kolonne A '
'--------------------------------------------------------------------------'
Dim ws As Worksheet
Dim lastRow As Long
'arnelgp
Dim yr As Long
Dim var As Variant
var = Split(Sheets("T").Range("h2").Value)
yr = Val(Sheets("T").Range(var(UBound(var))) & "")
Set ws = ThisWorkbook.Sheets("Bogføring")
ws.Range("$A$5:$J$65536").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/31/" & yr)
'Find sidste række i kolonne B
lastRow = [LOOKUP(2,1/(B1:B65536<>""),ROW(B1:B65536))]
'Sætter print området til sidste række
ws.PageSetup.PrintArea = ws.Range("A3:J" & lastRow).Address
'Viser overskriften på hvert print
ActiveSheet.PageSetup.PrintTitleRows = "$3:$5"
'Viser Dato - Klokken - Sidenummer i bunden af hvert print
ActiveSheet.PageSetup.CenterFooter = "&8Udskrevet d. &D & - & Kl. &T"
'Viser ikke printbreaks på fanen
ActiveSheet.DisplayAutomaticPageBreaks = False
'Åbner print
ActiveSheet.PrintOut 'Preview
'Opdaterer skærmen og hopper til sidste A2
ws.Range("$A$5:$J$65536").AutoFilter
ws.Range("$A$5:$J$65536").AutoFilter
Application.ScreenUpdating = True
ActiveSheet.Range("B65536").End(xlUp).Select
End Sub
BjarneHansen
02-05-2022, 07:27 AM
thanks again :bug::clap:
BjarneHansen
02-09-2022, 02:19 AM
var = Split(Sheets("T").Range("h2").Value)
what is this line doing H2 is emti i sheet "T"
arnelgp
02-09-2022, 03:14 AM
your H2 cell has "cellen C2"
if you use split() function it will create array with values:
array(0)="cellen"
array(1)="C2"
BjarneHansen
02-09-2022, 03:39 AM
ok but it cut take the year from C2 wher the input is
BjarneHansen
02-09-2022, 03:47 AM
yr = Sheets("T").Range("C2").Value & ""
like this i think :think:
arnelgp
02-09-2022, 04:20 AM
look at cell I2, what does it suggest?
BjarneHansen
02-09-2022, 04:26 AM
it tells you to input that year you work in
shut be input in C2
I2 is only info
arnelgp
02-09-2022, 04:33 AM
i Will walk you through the code:
var = Split(Sheets("T").Range("h2").Value)
var will be an array here.
Ubound(var) is the Upper Bound of the array.
the next line:
yr = Val(Sheets("T").Range(var(UBound(var))) & "")
var(Ubound(var)) will be "C2".
you plug it in the expression:
yr = Val(Sheets("T").Range("C2") & "")
yr= 2022
;;;;;;;;;;;
because you put Any "Range" into H2 (to point which year to process).
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.