PDA

View Full Version : Need to select a workbook which changes names everyday



Dovon
07-18-2019, 12:17 PM
I am creating a macro that works on any sheets. You start by opening an export file that we get every day and run the macro, it does some reformatting and then prompts the user to open another workbook which also has a different name each day "xxx-(date)" I would like the code to continue on that sheet but it doesn't count the sheet as activated when I open it so it does everything on the first sheet. here's my code:
Sub ACT()
'Selects the Initial Output
Dim io As Workbook: Set io = ActiveWorkbook
'Sets i as a variable
Dim i As Long
'sets variable i as the row number of the last cell filled
Range("K6").Select
Selection.End(xlDown).Select
i = ActiveCell.Row
'Fills to the last cell
Range("K6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]+RC[-3]+RC[-4]"
Range("K6").Select
Selection.AutoFill Destination:=Range("K6", "K" & i - 1)
'selects and copys the sums
Cells(6, 5).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
MsgBox "Please Open the Revised Workbook"
'selects the revised workbook
Dim re As Workbook: Set re = ActiveWorkbook
'pastes the sum values
re.Activate
Range("C2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim lrow As Long
lrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(3, 3).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, "I" & lrow).Select
Selection.Copy
io.Activate
Cells(6, 5).Select
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Kenneth Hobs
07-18-2019, 08:52 PM
Welcome to the forum! Please paste code between code tags. Click the # icon on reply toolbar to insert them.

Select, Selection, and Activate are seldom needed.

When you open a workbook, the last sheet worked on becomes the ActiveSheet.