Not sure what is the problem, but it might be that you are trying to open the master file itself. Can you move it to a different folder and then try again?
When it stops does it not give you an option to end or debug? If it does, press debug and see which line is highlighted. Do you know how to step through code using F8?
Are all the files you're opening Excel files and do they all have the Summary sheet?
Does the code do anything at all?
Don't think so. Which line errors?
Here is one approach. Adjust path etc to suit. The code goes in the master file.Code
Sub LoopThroughFolder() Dim oFSO As Object, sPath As String, wb As Workbook Set oFSO = CreateObject("Scripting.FileSystemObject") sPath = "C:\" 'path Application.ScreenUpdating = False For Each oFile In oFSO.GetFolder(sPath).Files Set wb = Workbooks.Open(oFile) wb.Sheets("Summary").Range("B9:K9").Copy _ ThisWorkbook.Sheets("Survey Summary").Range("A" & Rows.Count).End(xlUp)(2) wb.Close False Next oFile Application.ScreenUpdating = True End Sub
If you could post a sample workbook with a bit of data that would help. Not sure what you're trying to do. Are you trying to find ctyCol in rows 1 or 2? If so, you can use the Find method instead.
Not sure exactly where you are lost, but the main problem is that your condition can never be satisfied, hence no sheets were listed. If the name of a sheet is "Group 1" then
and "GRO" does not equal "Gro" (by default at least as case sensitive).
In summary I would amend your code as follows:Code
Private Sub Worksheet_Activate() Dim NextRw As Long Dim iX As Long Dim ws As Worksheet With Me .Range("A2:A30").ClearContents .Range("A2").Value = " Tab List" For Each ws In ThisWorkbook.Worksheets If UCase(Left(ws.Name, 3)) = "GRO" Then NextRw = .Cells(.Rows.Count, 3).End(xlUp).Row + 1 .Cells(NextRw, 3) = ws.Name End If Next ws End With End Sub
You have put it in the appropriate sheet module?
Anyway, if comparing upper case you need to use upper case![VBA]
If UCase(Left(ws.Name, 3)) = "GRO" [/VBA]I would also suggest moving your NextRw line inside the If statement otherwise you will have blank cells in your output.
Or should I just let go of the idea since going through 1 billion scenarios in Excel is not realistic?
This, I think. Find another bit of software or rationalise your problem. Do you really need to enumerate every single combination?
I'm not familiar with the gradient fill so what I would do in these circumstances is to record a macro and that should you give you the various properties and methods. You can then store the properties in an array or similar and apply them to other cells.
You can of course Copy and Paste formats.
Glad it worked. The first bit checks the intersect as we are not bothered if anything outside that range is changed. If a value other than "Yes" is entered don't you want to lock the cells? So you need to do something whether or not "Yes" is entered.
Why not define the fill up front depending on the project number and then apply the corresponding formatting depending on what's entered in the userform?
Try this. You can also reduce the If bit, but if you find it clearer you can retain your original formulation.
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B6:AC6")) Is Nothing Then Exit Sub
Target.Offset(1).Resize(19).Locked = (UCase(Target.Value) <> "YES")
I think it is working but in the 1RAYOTECLOGGER file you have some entries in around row 500 and the values are being transferred down there as the code is looking from the bottom up to find the last used row.
That's a lot of code. What does "[currency field]" represent?
There was a typo in that line which I corrected above, it should be
[vba]Set wbFrom = thisworkbook[/vba]
That works if the code is in the template file. Otherwise you need
[vba]Set wbFrom =Workbooks("Template.xlsm")[/vba] or whatever it's called.
The code above assumes both files are open. If they are not open, you will have to open them
[vba]Set wbFrom = Workbooks.Open("C:\Template...")[/vba]making sure you insert full path and file name. Similarly for wbTo.
Something like this, adjust as necessary as indicated in comments.
Dim wbFrom As Workbook, wbTo As Workbook
Dim vFrom As Variant, i As Long
vFrom = Array("A2", "B6", "S2", "S3", "S4", "S5", "AL38")
Set wbFrom = thisworkbook 'file containint code, or change to name of template file
set wbto = Workbooks("...") 'name of logger file
For i = LBound(vFrom) To UBound(vFrom)
wbTo.Sheets(1).Range("A" & Rows.Count).End(xlUp)(2).Value = wbFrom.Sheets(1).Range(vFrom(i)).Value 'adjust sheets and destination range as necessary
Hal - not sure what you're asking. Does this cover it?
Dim n As Long
n = Range("B3", Range("B" & Rows.Count).End(xlUp)).Count - 1
.Value = Range("G3").Value
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=Range("F3").Value, _
Stop:=Range("G3").Value + n * Range("F3").Value, Trend:=False