Automatic Filter-> Then Copy/Paste-> Print PDF

[WuAhUuU]

Hello,

I am having some difficulty with a macro which I would like to filter a query, then copy part of the query and paste that part in another sheet (However, when copying to the other sheet I need to tweak the paste funtion because column 4 from sheet Trade is actually column 5 from sheet Main). Then it prints the sheet in PDF format and resets the autofilter. Here is what I have so far:

Sub DailyCloseMacro()
Dim Data As Worksheet
Dim Trade As Worksheet
Dim Main As Worksheet
Set Data = Worksheets("Data")
Set Trade = Worksheets("TradingActivity")
Set Main = Worksheets("Main")

SilenceExcel
 
Date0D = Data.Cells(3, 2) [COLOR=darkgreen]'Today funtion (=TODAY())
[/COLOR]Date0 = format(Date0D, "mm/dd/yyyy") [COLOR=#006400]'Need this format to use the AutoFilter[/COLOR]
Trade.Range("$C$6:$H$50000").AutoFilter Field:=2, Criteria1:=Date0
[COLOR=darkgreen]'---
'CopyPaste Macro
'---
[/COLOR]Main.PrintOut Copies:=1, ActivePrinter:="PDFCreator on Ne00:"
Trade.Range("$C$6:$H$50000").AutoFilter Field:=2
WakeExcel
End Sub

Support macros

Public Sub SilenceExcel()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .StatusBar = "Calculating Macros - PLEASE WAIT"
End With
End Sub
 
Public Sub WakeExcel()
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = ""
End With
End Sub

Part of the sheet that has to be copied (NAME is H5):
<TABLE style="WIDTH: 271pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=361><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" span=2 width=68><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 51pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl675 height=15 width=68>TRADE DATE</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 83pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 width=110>NAME</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 width=47>STATUS</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 51pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl676 width=68>QUANTITY</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 51pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl676 width=68>PRICE</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl675 height=15> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl676> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl676> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl677 height=15>03/09/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>10-YR UST</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>OPEN</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673>(100)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl678>119.00000 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl677 height=15>03/10/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>10-YR UST</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>OPEN</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673>(100)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl678>118.75000 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl677 height=15>03/11/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>10-YR UST</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>OPEN</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673>100 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl678>119.00000 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl677 height=15>03/11/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>WTI CRUDE</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>OPEN</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673>5 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl678>100.00000 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl677 height=15>03/11/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>BRENT CRUDE</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671>OPEN</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673>(5)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl678>110.00000 </TD></TR></TBODY></TABLE>

The copy/paste part starts at name and goes until price. Trade sheet columns are continuous (no hiding between columns). Main sheet column 4 (price) is actually column 5 since column 4 is hidden.

Any brave soul??? User of Excel 2007

 

[WuAhUuU]

I have arranged the code below. It definitely needs tweaking and at the same time need to limit the copy to maximum of 10 rows from the TRADE sheet because the Main sheet will only allow 10 rows of paste information. Can anyone help me out?

I have arranged the following code:

Sub DailyCloseMacro()
Dim Data As Worksheet
Dim Trade As Worksheet
Dim Main As Worksheet

Set Data = Worksheets("Data")
Set Trade = Worksheets("TradingActivity")
Set Main = Worksheets("Main")

SilenceExcel
Main.Range("B31:F40").ClearContents
Date0D = Data.Cells(3, 2)
Date0 = format(Date0D, "mm/dd/yyyy")

Trade.Range("$C$6:$H$50000").AutoFilter Field:=2, Criteria1:=Date0
Trade.Select Trade.Range("H7:K7").Select [COLOR=#006400]'Cannot simply put this code without the line above this one or else error[/COLOR]
Trade.Range(Selection, Selection.End(xlDown)).Copy
Main.Range("$B$31").PasteSpecial Paste:=xlPasteValues
Main.Range("E31:E40").Copy
Main.Range("F31").PasteSpecial Paste:=xlPasteValues
Trade.Range("$C$6:$H$50000").AutoFilter Field:=2
Main.Select
Main.Range("A1").Select
WakeExcel
Main.PrintOut Copies:=1, ActivePrinter:="PDFCreator on Ne00:"
End Sub

HELP!!!! THANKS

 

[WuAhUuU]

I have arranged the code below. It definitely needs tweaking and at the same time I need to limit the copy range to a maximum of 10 rows from the TRADE sheet because the MAIN sheet will only allow 10 rows of paste information. Can anyone help me out? I was thinking something around if =COUNT(rows)>10, For i = 1 To 10, however, these formulas will get useless information inside the hidden rows.

NEED HELP!!!!!!

Thank you



I have arranged the following code:

Sub DailyCloseMacro()
Dim Data As Worksheet
Dim Trade As Worksheet
Dim Main As Worksheet

Set Data = Worksheets("Data")
Set Trade = Worksheets("TradingActivity")
Set Main = Worksheets("Main")
SilenceExcel
Main.Range("B31:F40").ClearContents

Date0D = Data.Cells(3, 2)
Date0 = format(Date0D, "mm/dd/yyyy")

If Trade.Range("H7") = "" Then GoTo finish Else
Trade.Range("$C$6:$H$50000").AutoFilter Field:=2, Criteria1:=Date0
Trade.Select
Trade.Range("H7:K7").Select
Trade.Range(Selection, Selection.End(xlDown)).Copy
Trade.Range("A1").Select
Main.Range("$B$31").PasteSpecial Paste:=xlPasteValues
Main.Range("E31:E40").Copy
Main.Range("F31").PasteSpecial Paste:=xlPasteValues
finish:
Trade.Range("$C$6:$H$50000").AutoFilter Field:=2
Main.Select
Main.Range("A1").Select

WakeExcel
Main.PrintOut Copies:=1, ActivePrinter:="PDFCreator on Ne00:"
End Sub

SUPPORT MACROS

Public Sub SilenceExcel()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .StatusBar = "Calculating Macros - PLEASE WAIT"
End With

End Sub
Public Sub WakeExcel()
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = ""
End With
End Sub