Hi,
I have 3 macros that I need help with to make it run through:
Code:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+e
' ***** From file called May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx Tab Sheet1 to current file Tab called Sheet22 *****
'
'
Workbooks.Open Filename:= _
"C:\Users\rzepeckir\Documents\Vendor Current Month\May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx"
Selection.AutoFilter
ActiveCell.Offset(-17, -6).Range("A1:P224").Select
Selection.Copy
Windows("VBA Extractor r57with code.xlsm").Activate
'ActiveCell.Offset(-1, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+f
'
'***** From file called Copy of Vendor Master Pole set File 06-12-18.xlsx Tab Sheet1 to current file Tab called Sheet20 *****
'
'
Workbooks.Open Filename:= _
"C:\Users\rzepeckir\Documents\Vendor Current Month\Copy of Vendor Master Pole set File 06-12-18.xlsx"
Selection.AutoFilter
ActiveCell.Offset(0, 0).Range("A2:AQ40000").Select
Selection.Copy
Windows("VBA Extractor r57with code.xlsm").Activate
'ActiveCell.Offset(-1, -1).Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
' Refresh all applicable pivot tables to setup month's data
Sub refresh()
'
' refresh Macro
' refresh data
'
' Keyboard Shortcut: Ctrl+r
MsgBox ("Update may take several minutes, Click Ok to begin")
' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)
ActiveWorkbook.RefreshAll
' Refresh all Pivot tables in all worksheets
Dim shtTemp As Worksheet
Dim pvtTable As PivotTable
For Each shtTemp In ActiveWorkbook.Worksheets
For Each pvtTable In shtTemp.PivotTables
'pvtTable.RefreshTable
Next
Next
MsgBox ("Update Complete,All data is Up-to date")
End Sub
Public Function save()
End Function
/End code
I would really like to have this run through once and need a bit of help on naming sheets in the workbook that would copy from and paste to. Any and all responses would be appreciated.
Thanks,
Ray
I have 3 macros that I need help with to make it run through:
Code:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+e
' ***** From file called May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx Tab Sheet1 to current file Tab called Sheet22 *****
'
'
Workbooks.Open Filename:= _
"C:\Users\rzepeckir\Documents\Vendor Current Month\May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx"
Selection.AutoFilter
ActiveCell.Offset(-17, -6).Range("A1:P224").Select
Selection.Copy
Windows("VBA Extractor r57with code.xlsm").Activate
'ActiveCell.Offset(-1, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+f
'
'***** From file called Copy of Vendor Master Pole set File 06-12-18.xlsx Tab Sheet1 to current file Tab called Sheet20 *****
'
'
Workbooks.Open Filename:= _
"C:\Users\rzepeckir\Documents\Vendor Current Month\Copy of Vendor Master Pole set File 06-12-18.xlsx"
Selection.AutoFilter
ActiveCell.Offset(0, 0).Range("A2:AQ40000").Select
Selection.Copy
Windows("VBA Extractor r57with code.xlsm").Activate
'ActiveCell.Offset(-1, -1).Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
' Refresh all applicable pivot tables to setup month's data
Sub refresh()
'
' refresh Macro
' refresh data
'
' Keyboard Shortcut: Ctrl+r
MsgBox ("Update may take several minutes, Click Ok to begin")
' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)
ActiveWorkbook.RefreshAll
' Refresh all Pivot tables in all worksheets
Dim shtTemp As Worksheet
Dim pvtTable As PivotTable
For Each shtTemp In ActiveWorkbook.Worksheets
For Each pvtTable In shtTemp.PivotTables
'pvtTable.RefreshTable
Next
Next
MsgBox ("Update Complete,All data is Up-to date")
End Sub
Public Function save()
End Function
/End code
I would really like to have this run through once and need a bit of help on naming sheets in the workbook that would copy from and paste to. Any and all responses would be appreciated.
Thanks,
Ray
Last edited: