Solved: VBA Macro to Print

[ports18]

Hi, am new to this site and VBA but heard it was a great sight to look into. I've been asked to investigate if its possible to have a macro enter in a PCID one at a time from say column A on another sheet and print as it goes, so it would put in the 1st PC ID thats located in column A in sheet 2 into a selected Cell (Say C4), print it then go onto the next PCID down. This way instead on manually putting in PC ID's and printing 1 at a time, you run the macro and it prints all PCID's in column A.

Cheers,
Glen

[mvidas]

Hi Glen,

Give the following a try, just set the sheets/ranges at the top and run![vba]Sub ports18()
Dim SheetWithPCIDs As Worksheet, SheetToPrint As Worksheet
Dim PCIDcol As Range, CellToPasteTo As Range, CLL As Range

'***** CHANGE AS NEEDED *****
Set SheetWithPCIDs = Sheets("Sheet with PCIDs")
Set SheetToPrint = Sheets("Sheet To Print")
Set PCIDcol = SheetWithPCIDs.Columns("A")
Set CellToPasteTo = SheetToPrint.Range("C4")

Set PCIDcol = Intersect(PCIDcol, SheetWithPCIDs.UsedRange) ', SheetWithPCIDs.Rows("2:65536"))
For Each CLL In PCIDcol.Cells
CellToPasteTo.Value = CLL.Value
SheetToPrint.PrintOut
Next
End Sub[/vba]If your column with PCIDs has a header row, then include the commented out portion in the Intersect statement (skips row 1 when pasting/printing). Please let us know if you have any questions!
Matt

[ports18]

Thanks for your Help, being new to this I have no idea what Im doing, hence starting a course soon but I have changed the code to what i thought related to my Worksheets etc. When I run it I get a "Method or Data member not found" and it highlights Value = on the 4th last row. Could you let me know where I have it wrong.

Appreciate the assistance.

Glen


[VBA] Sub Ports18()
Dim SheetApp_List As Worksheet, SheetBuild_Sheet As Worksheet
Dim PCIDcol As Range, CellTo As Range, CLL As Range

'***** CHANGE AS NEEDED *****
Set SheetApp_List = Sheets("Sheet with PCIDs")
Set SheetBuild_Sheet = Sheets("Sheet To Print")
Set PCIDcol = SheetApp_List.Columns("A")
Set CellToPasteTo = SheetBuild_Sheet.Range("H3")

Set PCIDcol = Intersect(PCIDcol, SheetApp_List.UsedRange) ', SheetApp_List.Rows("2:65536"))
For Each CLL In PCIDcol.Cells
SheetBuild_Sheet.Value = CLL.Value
SheetBuild_Sheet.PrintOut
Next
End Sub[/VBA]

[geekgirlau]

Hi Glen,

You need to change the sheet names in the code. For example, you probably don't have a sheet named "Sheet with PCIDs", so you need to replace it with the name of the sheet that contains the PC IDs.

[ports18]

Like this ? I'm not sure what to put for CLL as Range, what is CLL ?

[VBA]Dim App_List As Worksheet, Build_Sheet As Worksheet
Dim App_List("A:A") As Range, Build_Sheet("H3") As Range, CLL As Range[/VBA]

Cheers,
Glen

[ports18]

OK, I have a Runtime Error 9, Subscript out of range, am getting it here
[VBA]Set App_List = Sheets("Sheet with PCIDs")[/VBA]

What am I missing or what have I done wrong. This is taking me back to Tafe Days ! !haha

[VBA]Sub ports18()
Dim App_List As Worksheet, Build_Sheet As Worksheet
Dim PCIDcol As Range, CellTo As Range, CLL As Range

'***** CHANGE AS NEEDED *****
Set App_List = Sheets("Sheet with PCIDs")
Set Build_Sheet = Sheets("Sheet To Print")
Set PCIDcol = App_List.Columns("A:A")
Set CellToPasteTo = Build_Sheet.Range("H3")

Set PCIDcol = Intersect(PCIDcol, App_List.UsedRange) ', App_List.Rows("2:65536"))
For Each CLL In PCIDcol.Cells
CellToPasteTo.Value = CLL.Value
Build_Sheet.PrintOut
Next
End Sub[/VBA]

[Zack Barresse]

Glen, [uvba]here[/uvba] Hope you don't mind, but Johnske and I have edited your posts to include them.

[ports18]

Sorry Guys, thanks for cleaning it up. Ok I have the code working now and it works great, bar 1 problem. here is the code

[VBA] Sub ports18()
Dim App_List As Worksheet, Build_Sheet As Worksheet
Dim PCIDcol As Range, CellToPasteTo As Range, CLL As Range

'***** CHANGE AS NEEDED *****
Set App_List = Sheets("App_List")
Set Build_Sheet = Sheets("Build_Sheet")
Set PCIDcol = App_List.Columns("A19:A28")
Set CellToPasteTo = Build_Sheet.Range("H3")

Set PCIDcol = Intersect(PCIDcol, App_List.UsedRange) ', App_List.Rows("2:65536"))
For Each CLL In PCIDcol.Cells
CellToPasteTo.Value = CLL.Value
Build_Sheet.PrintOut
Next
End Sub [/VBA]

The problem is on the App_List sheet it lists the PCID's in Colum A, on Column D it has the applications. Because of the format of the list you can get multiple PC ID's with different Apps, ie
PC22408 Notes
PC22408 Word
PC22408 Office
So if the PC has 10 apps it will list it 10 times with the different apps. How can I make the code Pickup only 1 of the duplicates and print it and then go to the next one ? Currently it Prints all cells in Column A.

Hope this made sense.................Cheers

[mvidas]

Hi Glen,

I changed it to suit your needs, what I did was create an array for you to put the PCIDs in after printing, so you can make sure you don't print the same one twice. I also included a different way of assigning the PCIDcol variable, so it dynamically chooses the range instead of hardcoding like you did (with A19:A28). Please don't hesitate to ask if you have any questions![vba]Sub ports18()
Dim App_List As Worksheet, Build_Sheet As Worksheet
Dim PCIDcol As Range, CellToPasteTo As Range, CLL As Range
Dim UsedPCIDs() As String, Cnt As Long, i As Long, aPCID As String

'***** CHANGE AS NEEDED *****
Set App_List = Sheets("App_List")
Set Build_Sheet = Sheets("Build_Sheet")
Set CellToPasteTo = Build_Sheet.Range("H3")
Set PCIDcol = App_List.Range("A19:A28")
'uncomment the next line to set pcidcol with used cells starting with A19
'Set PCIDcol = App_List.Range("A19", App_List.Range("A65536").End(xlUp))

ReDim UsedPCIDs(0)
Cnt = 0
Set PCIDcol = Intersect(PCIDcol, App_List.UsedRange) ', App_List.Rows("2:65536"))
For Each CLL In PCIDcol.Cells
aPCID = CLL.Value
For i = 0 To Cnt - 1
If UsedPCIDs(i) = aPCID Then Exit For
Next
If i = Cnt Then
ReDim Preserve UsedPCIDs(Cnt)
UsedPCIDs(Cnt) = aPCID
Cnt = Cnt + 1
CellToPasteTo.Value = aPCID
Build_Sheet.PrintOut
End If
Next
End Sub[/vba]Matt

[ports18]

Worked great.

Thanks very much for your time Matt, appreciate it.

Cheers,
Glen.

[mvidas]

Glad to help!
Since you're new to the forum, when an issue has been solved, you can close the thread by going to Thread Tools at the top of the page, then choosing 'Mark Thread Solved'. This prepends 'Solved: ' to the thread title. I've done it for this one, just letting you know for the future

Let us know if you need anything else!
Matt