Solved VBA to apply a vlookup but prompts for a file name?

July 22, 2015 at 10:41:31
Specs: Windows 7
Hello currently i am working on automating a report process to create a sheet from pivot tables. The next step is to fill in columns Q, R, S with verbiage from another Book saved on Sharepoint. Both Excel files are housed on sharepoint. Is it possible to add to the below macro to add the vloopkup to the columns listed above that references a sheet that doesnt exist at the beginning of the month but will in the second week? Example currently using the macro below it generates my report columns Q, R, and S will be blank the first week but second week it will not. I need a VBA to copy that commentary over to the newly generated file each week that i can then Save As and overwrite the existing file saved on sharepoint. See code below.

Sub ARC()
'
' ARC Macro
'
Dim ws As Worksheet
Dim SelectedCell As Range

Range("C13").Select
Selection.ShowDetail = True

ActiveSheet.Name = "ARC"

Columns("A:C").Select
Selection.Delete Shift:=xlToLeft

Columns("P:AG").Select
Selection.Delete Shift:=xlToLeft

Columns("A:D").ColumnWidth = 11
Columns("E:E").ColumnWidth = 6
Columns("F:F").ColumnWidth = 8
Columns("G:G").ColumnWidth = 8.29
Columns("H:H").ColumnWidth = 22
Columns("J:J").ColumnWidth = 55
Columns("K:K").ColumnWidth = 11
Columns("L:L").ColumnWidth = 5.71
Columns("M:M").ColumnWidth = 4
Columns("N:N").ColumnWidth = 4

Range("P1").Select
ActiveCell.FormulaR1C1 = "My Comments"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Final Decision"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Opportunity"

Columns("P:P").ColumnWidth = 17
Columns("R:R").ColumnWidth = 12.43

Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 8
End With

ActiveWindow.Zoom = 80

Cells.Select
With Selection
.WrapText = True
End With

Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "#"
Range("B2").Select
ActiveCell.FormulaR1C1 = "= SUBTOTAL(2, R1C[6]:RC[6])"
Columns("B:B").ColumnWidth = 5

' New workbook
Sheets("ARC").Select
Sheets("ARC").Move

' Sub UploadToSharepoint()

Dim SharepointAddress As String
Dim LocalAddress As String
Dim DestFile As String
Dim SourceFile As String
Dim objNet As Object
Dim FS As Object

SharepointAddress = "Sharepointlink\"

LocalAddress = "Sharepointlink\"

SourceFile = "Automation Project.xlsm"
DestFile = "Generated ARC.xlsm"

Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")

ActiveWorkbook.SaveAs Filename:=SharepointAddress & DestFile _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Set objNet = Nothing
Set FS = Nothing

Windows("Generated ARC.xlsm").Activate

Range("A1").Select
ActiveWorkbook.Save

End Sub

message edited by phatguy85


See More: VBA to apply a vlookup but prompts for a file name?

Report •


#1
July 22, 2015 at 11:21:47
✔ Best Answer
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. By using the pre tags, you will be able to retain the indentation of the code and make it easier for us to follow.

May I also suggest that you clean up your code to make it both easier to read and more efficient. For example, it is rarely required that you Select a cell within VBA in order to perform and action on it.

For example, these instructions...

Range("P1").Select
ActiveCell.FormulaR1C1 = "My Comments"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Final Decision"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Opportunity"

...can (should) be written as:

Range("P1") = "My Comments"
Range("Q1") = "Final Decision"
Range("R1") = "Opportunity"

Without reviewing your code in it's entirety, I'm fairly confident in saying that you can probably eliminate every instance of .Select and condense multiple lines of code into a single instruction.

That said, I'm not 100% sure that I understand your question:

Is it possible to add to the below macro to add the vloopkup to the columns listed above that references a sheet that doesnt exist at the beginning of the month but will in the second week?

Are you looking for VBA code that will allow you construct a VLOOKUP formula that references a non-existent workbook without it causing an error, then when the workbook is created, it will return the desired value? e.g., in English:

"If MyFile does not exist, return a blank cell but if MyFile does exist, perform a VLOOKUP referencing that file."

Am I close?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question