Help with print macro

[UteCarl]

I use this print macro to send a line of data from my spreadsheet to a form and then the printer. It works fine in the one spreadsheet, but I have numerous sheets (monthly) in the workbook and my problem is that it won't work on any of the other sheets other than the one it is created in.

My question is how to make this work for all worksheets in the workbook. Thank you for any advice.

I will post the 'error' part of it below the actual macro to maybe help you troubleshoot!

Carl

Sub Print2018()
'
' Print2018 Macro
' Claimed Item Receipt.  Place the cursor in the Found Item field prior to printing!
'

'
    ActiveCell.Offset(0, -1).Range( _
        "Table1[[#Headers],[Article Number]:[Claimed By]]").Select
    Selection.Copy
    Sheets(".").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Rows.AutoFit
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Selection.ClearContents
    Sheets("Jun 2018").Select
    ActiveCell.Offset(0, 1).Range("Table1[[#Headers],[Article Number]]").Select
End Sub

..... the error that comes up when I try to print on a different sheet in the workbook (highlights in yellow and can't get past) is as follows:

ActiveCell.Offset(0, -1).Range( _
"Table1[[#Headers],[Article Number]:[Claimed By]]").Select

 

[chirayu]

Can't really check without a sample file

 

[Hui]

Carl

Firstly, Welcome to the Chandoo.org Forums

Can you upload a sample file and describe what you want to achieve?

 

[UteCarl]

Carl

Firstly, Welcome to the Chandoo.org Forums

Can you upload a sample file and describe what you want to achieve?

Thank you - I'm new to this!!

 

[Hui]

Try the following code:

Sub Print_All()
'
' Print2018 Macro
' Claimed Item Receipt.  Place the cursor in the Found Item field prior to printing!
'
Dim sh As Worksheet
For Each sh In Worksheets
  If sh.Name <> "." Then
    sh.Activate
    Range("B2").Select
   
    ActiveCell.Offset(0, -1).Range(ActiveCell.ListObject.Name & "[[#Headers],[Article Number]:[Claimed By]]").Select
    Selection.Copy
    Sheets(".").Select
    ActiveSheet.Paste
   
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Rows.AutoFit
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Selection.ClearContents
  End If
Next sh

End Sub

 

[UteCarl]

Hello and thank you for that! There are a couple of things it is doing that I'm hoping you can still help me with...

1. It is printing a sheet for every month (each month is a separate sheet) - no matter which month you begin in, it will print one sheet for each month. I only need the 'active' sheet to print - the one we are working on (i.e. June right now, July next month, etc.); and

2. I had it so that it would print only the information on the line where we placed the cursor. This macro always prints only the first line of entry for each sheet. As we enter data for the lost items, and someone then claims an item - I need it to print THAT ITEM, which might be 200 lines down the spreadsheet, not the first line.

Thank you once again for your continued assistance Hui.

 

[Hui]

Sub Print_Active()
'
' Print_Active Macro
' Prints Claimed Item Receipt.
' Place the cursor in the Found Item field prior to printing!
'
   
  Range("B" & ActiveCell.Row, "M" & ActiveCell.Row).Copy Sheets(".").Range("A2")
   
  Sheets(".").Select
  Range("A2:L2").Select
  With Range("A2:L2")
  .Interior.Pattern = xlNone
  .Interior.TintAndShade = 0
  .Interior.PatternTintAndShade = 0
  .Rows.AutoFit
  End With
  ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
  Selection.ClearContents
End Sub

 

[UteCarl]

You are a GENIUS Hui! It works like a charm. I am happy to keep this just the way it is, however if there would be a way to return the 'cursor' to the spot where it originated (back to the worksheet (month) that is being used) that would just be icing on the cake! When the routine - which works fine - is done, it stops on the print sheet. This might be because it would change for every month due to a different 'sheet' being used each month???? Either way I am very grateful for your assistance.

 

[Hui]

Sub Print_Active()
'
' Print_Active Macro
' Prints Claimed Item Receipt.
' Place the cursor in the Found Item field prior to printing!
'
   
Dim StartSht As String, StartAdd As String

' Setup
StartSht = ActiveSheet.Name
StartAdd = ActiveCell.Address

' Copy current row to .
Range("B" & ActiveCell.Row, "M" & ActiveCell.Row).Copy Sheets(".").Range("A2")

' Format
Sheets(".").Select
Range("A2:L2").Select
With Range("A2:L2")
  .Interior.Pattern = xlNone
  .Interior.TintAndShade = 0
  .Interior.PatternTintAndShade = 0
  .Rows.AutoFit
End With
' Print
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Selection.ClearContents

' Return to start location
Worksheets(StartSht).Select
Range(StartAdd).Select
End Sub

 

[UteCarl]

Hui you are fantastic. This works like a charm and will definitely help us perform our customer service with added ease and professionalism. I truly appreciate your assistance sir!