donatepresent
New Member
- Joined
- Oct 4, 2017
- Messages
- 21
Hi Experts,
I'm trying to make my code more dynamic rather than static so, I want to replace the following code:
Instead of defined 'Range("BD3:BD41")' in above OpenBook range, I want it to do the below code or better yet find the first column in the Pivot Table [in OpenBook.Sheets("Programs")] that contains "Grand Total" (would be in Row 4) and copy from that column from 'Row 3 to Row 41' and paste to ActiveWorkbook.Sheets("Sheet2") in BE3 or better yet the column right of the last column <-- see full code below:
Full code:
Thanks ,
Ken
I'm trying to make my code more dynamic rather than static so, I want to replace the following code:
VBA Code:
FileToOpen = Application.GetOpenFilename
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets("Programs").Range("BD3:BD41").Copy .Range("BE3")
OpenBook.Close False
Instead of defined 'Range("BD3:BD41")' in above OpenBook range, I want it to do the below code or better yet find the first column in the Pivot Table [in OpenBook.Sheets("Programs")] that contains "Grand Total" (would be in Row 4) and copy from that column from 'Row 3 to Row 41' and paste to ActiveWorkbook.Sheets("Sheet2") in BE3 or better yet the column right of the last column <-- see full code below:
VBA Code:
Range("D4").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 1).Activate
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range ("BE3")
Full code:
VBA Code:
Application.AskToUpdateLinks = False
Dim FileToOpen As Variant
Dim OpenBook As Workbook
With ActiveWorkbook.Sheets("Sheet2")
MsgBox "Please select last month FS file"
FileToOpen = Application.GetOpenFilename
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets("Programs").Range("BD3:BD41").Copy .Range("BE3")
OpenBook.Close False
With .Range("D44", Cells(43, Columns.Count).End(xlToLeft).Offset(1))
.FormulaR1C1 = _
"=IFERROR(HLOOKUP(R[-38]C,'[FileToOpen]Programs'!R6C4:R41C55,36,FALSE),"""")"
.Style = "Comma"
End With
End With
Range("D43").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("D44").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.AskToUpdateLinks = True
Thanks ,
Ken