Replies

Got it thanks.

Hans, one last question, I am trying to write a macro to include this formula and populate the column for all the rows that have data. How can I go about doing this. When I also put the macro for the formula, I get a red ink on the line of coding and it refers back to the “-“. Any help would be great.

Sub CleanUpSheet()
‘
‘ CleanUpSheet Macro

‘

‘
Columns(“A:D”).Select
Selection.Delete shift:=xlToLeft
Columns(“B:B”).Select
Selection.ClearContents
Columns(“A:C”).Select
Columns(“A:C”).EntireColumn.AutoFit
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “PCA”
Columns(“A:B”).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “”
.ErrorTitle = “”
.InputMessage = “”
.ErrorMessage = “”
.ShowInput = True
.ShowError = True
End With
Range(“B2”).Select
Selection.FormulaArray = “=1*MID(A1,MAX(ROW($1:$100)*(MID(A1,ROW($1:$100),1)=”_”))+1,255)”
Selection.AutoFill Destination:=Selection
Range(“A1”).Select
End Sub

Thanks Hans.

Thank you hans.

Below is the macro to insert a row and assign names to columns.

Rows(“1:1”).Select
Selection.Insert Shift:=xlDown
Columns(“A:O”).Select
Columns(“A:O”).EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-12
Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Name”
Rows(“1:1”).Select
Selection.Font.Bold = True
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “Month”
Range(“C1”).Select
ActiveCell.FormulaR1C1 = “Index”
Range(“D1”).Select
ActiveCell.FormulaR1C1 = “Car Type”
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “model Type”
Range(“F1”).Select
ActiveCell.FormulaR1C1 = “Survey Activity”
Range(“G1”).Select
ActiveCell.FormulaR1C1 = “Day”
Range(“G1”).Select
Selection.AutoFill Destination:=Range(“G1:N1”), Type:=xlFillDefault
Range(“G1:N1”).Select
Range(“O1”).Select
ActiveCell.FormulaR1C1 = “Total”
Range(“O2”).Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

Thanks.

Hans,

Is there a way to add to the macro a command to put a desktop shortcut for the created map drive J?

Ok.

Hans,
Can you tell me via which program I can do this? I would like to do it in an excel file or word. By the way, you are correct about the //.

Hans you are a genius. This is exactly what I need. Thanks.

Hello Hans. The first statement on number 2 is correct. The sheet that I need begins with a “block” on it.

Hans, each workbook (file) has three sheets in it. However, I am only trying to pull one sheet from these workbooks. I was wondering if there is an if statement which can pull a sheet as is processing the workbook. The if statement would say
If Sheet name in workbook is “block1” or “block 2” or “block 3” or “block 4” then copy the sheet to the “recap” workbook. Let me know if this helps, if not, I can always run the macro, but will have to select all files with block 1 then all files with block 2 and so forth.

Thanks. I ended up calling Linksys and found out that when the apple laptop ask for a password you have to put a dollar sign and then a key associated with the WEP. Thanks for the links.

I am sorry Hans. I made some modifications to the names of the files from “1st half” to “block”. Each file has a sheet name call “block” too. Below is the new code

Sub Processfiles()
‘ Variable Declaration
Dim vFile As Variant
Dim FilesToOpen
Dim iFileCount As Integer
Dim x As Integer
Dim wbk As Workbook
On Error GoTo ErrHandler
Application.ScreenUpdating = False

‘ Get files to work with
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add “Excel files”, “*.xls”

.InitialFileName = _
“C:adminNew Code StructuresNew Timesheet*block*.xls”
.AllowMultiSelect = True
If .Show = True Then
‘ Act on each file
For Each vFile In .SelectedItems
‘ Open it
Set wbk = Workbooks.Open(Filename:=vFile)
‘ Transfer sheet
wbk.Sheets(“block 1”).Copy Before:=Workbooks(“recap.xls”).Sheets(1)—————————–>when I run this macro and the sheet name is not “block 1” I get an error. The files I will be selecting can either have block 1,2,3 or 4. This is where I am running into the error. I don’t know if it makes sense or not, but I need to be able to select multiple files and different sheets (block 1, 2, 3, or 4)?
‘ Close it
wbk.Close SaveChanges:=False
Next vFile
End If
iFileCount = .SelectedItems.Count
End With

‘ Give a message saying you are done
If iFileCount = 1 Then
MsgBox “1 File was processed”
Else
MsgBox iFileCount & ” Files were processed”
End If

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Hans,
the sheet to copy will either be block 1, block2, block 3, or block four depending on the file is processing. However, it will only be one. For example if it selects (“1-15″ 1st Half”). It should process sheet (“block 1”). Each selected file will have a sheet called block with it. For example, (“1-15 1st Half”) will only have block 1. File (“16-30″ 2nd half”) will have a sheet call (“block 2”) Does that help?