Using Excel 2010
This is going to sound really messy, so I hope you'll forgive me as I'm new to VBA.
Let me outline what I'm trying to do:
Column A: User fills in file name
Column B: Formula to concatenate file name to external file path to create formula (it references cell A2 in different workbooks): =IF(A3<>0,CONCATENATE("='\\Network\Folder\[",A3,".xlsm]CT Data'!A2"),"")
Column C: Formula from Column B is copied over to C and has "=" replaced in order to force calculation of formula using Macro
Columns D-W: I want to autofill based upon the external cell reference from Column C
THEN
Column C needs to be formatted as a Percentage
Columns J and K need to be formatted as Short Dates
All others need to be formatted as General
I've gotten this to work using 1 macro but it's really slow and I'm sure it's not the most efficient code. Essentially, I've identified the range as 50 rows, so it's performing these functions for all 50 every time I perform the macro. So I'd like to insert code that says "If C3 in each row <> 0, then autofill each column through Column W, then perform required formatting" so that it doesn't repeat for all 50 rows each time I run it. Any help is much appreciated!
Here's the code I'm currently using:
Sub Fill()
'
' Fill Macro
'
'
ActiveSheet.AutoFilterMode = False
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C3").Select
Range("C3:C50").Select
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C3").Select
Range("C3:C50").Select
Selection.Autofill Destination:=Range("C3:W50"), Type:=xlFillDefault
Range("C3:W50").Select
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00%"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0%"
Range("D3:I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"
Range("J3:K3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Range("L3:W3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"
Range("C3").Select
End Sub
This is going to sound really messy, so I hope you'll forgive me as I'm new to VBA.
Let me outline what I'm trying to do:
Column A: User fills in file name
Column B: Formula to concatenate file name to external file path to create formula (it references cell A2 in different workbooks): =IF(A3<>0,CONCATENATE("='\\Network\Folder\[",A3,".xlsm]CT Data'!A2"),"")
Column C: Formula from Column B is copied over to C and has "=" replaced in order to force calculation of formula using Macro
Columns D-W: I want to autofill based upon the external cell reference from Column C
Column D: ='\\Network\Folder\[File Name.xlsm]CT Data'!B2
Column E: ='\\Network\Folder\[File Name.xlsm]CT Data'!C2
And so on.
Column E: ='\\Network\Folder\[File Name.xlsm]CT Data'!C2
And so on.
THEN
Column C needs to be formatted as a Percentage
Columns J and K need to be formatted as Short Dates
All others need to be formatted as General
I've gotten this to work using 1 macro but it's really slow and I'm sure it's not the most efficient code. Essentially, I've identified the range as 50 rows, so it's performing these functions for all 50 every time I perform the macro. So I'd like to insert code that says "If C3 in each row <> 0, then autofill each column through Column W, then perform required formatting" so that it doesn't repeat for all 50 rows each time I run it. Any help is much appreciated!
Here's the code I'm currently using:
Sub Fill()
'
' Fill Macro
'
'
ActiveSheet.AutoFilterMode = False
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C3").Select
Range("C3:C50").Select
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C3").Select
Range("C3:C50").Select
Selection.Autofill Destination:=Range("C3:W50"), Type:=xlFillDefault
Range("C3:W50").Select
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00%"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0%"
Range("D3:I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"
Range("J3:K3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Range("L3:W3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"
Range("C3").Select
End Sub