Posts by yogin

yogin · Dec 16th 2013

Re: Auto populate date to seperate tables in one sheet

not sure if you are happy with solution without using VBA.

yogin · Dec 16th 2013

Re: COUNTIFS - ANY DATA IN COLUMN A and ANY DATA IN COLUMN B

not sure if this solution helps you, the other option will be through VBA i belive.

yogin · Dec 16th 2013

Re: If Statement to Indicate Leave

it would good to see a sample book, but put into a direction you can use the below if statement:

=IF(SUMIF(A:A,B2,C:C)>0,SUMIF(A:A,B2,C:C),"OFF")

yogin · Dec 16th 2013

Re: Alter column width for weekends automatically

i hope this is what you are looking for? if you change the cell B2 with new month date it will update the file for you.

yogin · Dec 12th 2013

Re: VBA- Copying data from multiple Excel files

in the file you have this information, which sheet and cell this information is on? for example if you wanted Account 507 in cell B9 where will this information be in file 18112013?

Also what is the file name saved as? are they 18112013 or 181113 or something else? Also will these files be in same location?

yogin · Dec 10th 2013

Re: VBA- Copying data from multiple Excel files

why do you need a macro? is it just one cell value you need to copy into the master file? if so just use indirect funtion.

yogin · Dec 10th 2013

Re: Macro to paste values into column that matches date

do you have the date in cell AG2? as the macro is using that cell to paste the value.

yogin · Dec 9th 2013

Re: Removing (-) dash from a cell

you can also try one without macro.

yogin · Dec 9th 2013

Re: Macro to paste values into column that matches date

hope this is what you are looking for? it is based on what date you have in cell AG2 which is define the column it needs to paste the data into.

yogin · Dec 9th 2013

Re: VLOOKUP with multiple values - do I need a helper table?

there is a work around for this as you have mutilple lines where the name is different so its easy to create fresh lookup for that which you can keep updating if things change. Hope this helps

yogin · Dec 6th 2013

Re: Select a range of cells and give it a name

try the below code:

Code

Sub NRAN()
Dim strName As String
 
    strName = InputBox(Prompt:="Enter Range Name: The first character of a name must be a letter, an underscore character (_), or a backslash (\). Names cannot be the same as a cell reference, such as Z$100 or R1C1.Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.,A name can contain up to 255 characters.", _
          Title:="ENTER RANGE NAME WITHOUT SPACE", Default:="ABC")
          
        If strName = "ABC" Or _
           strName = vbNullString Then
           Exit Sub
        Else
    Cells.Select
    ActiveWorkbook.Names.Add Name:=strName, RefersToR1C1:="=Sheet1!R1:R1048576"
    ActiveWorkbook.Names(strName).Comment = ""
    
    End If
End Sub

Display More

yogin · Nov 3rd 2012

Re: move files to selected folder - code help

i think i got it and its works fine...

Code

Sub Move_files()
Dim fso
Dim fol As String








Set fso = CreateObject("Scripting.FileSystemObject")




oldpath = Range("B1").Value & "\"
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    For j = 4 To LR
        initname = Cells(j, 1).Text
        newPath = oldpath & Cells(j, 2).Text & "\"
        
        If Not fso.folderexists(newPath) Then
            fso.CreateFolder (newPath)
            FileInQuestion = Dir(oldpath & initname & "*.*")
                If FileInQuestion <> "" Then
             '     MsgBox oldpath & FileInQuestion & " As " & newPath & FileInQuestion
                Name oldpath & FileInQuestion As newPath & FileInQuestion
            End If
        Else
        FileInQuestion = Dir(oldpath & initname & "*.*")
            If FileInQuestion <> "" Then
                '     MsgBox oldpath & FileInQuestion & " As " & newPath & FileInQuestion
                Name oldpath & FileInQuestion As newPath & FileInQuestion
            End If
        End If
    Next
End Sub

Display More

yogin · Nov 3rd 2012

Re: move files to selected folder - code help

Thanks Patel this works very well however is it possible to create the folder if it does not exist?

yogin · Nov 2nd 2012

Re: move files to selected folder - code help

yes that right, so in the file it should save the file name starting with Bondi (Cell A4) to C:\test\01. MC...

yogin · Nov 2nd 2012

hi can someone please help me. I need Macro button which can move all the files which start Name in column A and move then to a folder in the same drive which is mentioned in Column B.

for example, I will enter the path where i need this to done which is cell B2. And then the macro should run and move all files which starts with Bondi (A4 cell) and move to folder which is mentioned cell B4. in case the folder does not exist then it should create the folder and move the file.

i do this daily for 500 files and move them into 20 to 23 folder each day. if some one can please create this it will help me alot. i did find few code on internet but not that good with VBA to make it work for me.

Move file if its exist:

Code

Sub MoveFile()Dim fsoDim file As String, sfol As String, dfol As Stringfile = "test.xls" ' change to match the file namesfol = "C:\" ' change to match the source folder pathdfol = "E:\" ' change to match the destination folder pathSet fso = CreateObject("Scripting.FileSystemObject")If Not fso.FileExists(sfol & file) Then    MsgBox sfol & file & " does not exist!", vbExclamation, "Source File Missing"ElseIf Not fso.FileExists(dfol & file) Then    fso.MoveFile (sfol & file), dfolElse    MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"End IfEnd Sub

Create folder if it does not exist:

Code

Sub CreateFolder()Dim fsoDim fol As Stringfol = "c:\MyFolder" ' change to match the folder pathSet fso = CreateObject("Scripting.FileSystemObject")If Not fso.FolderExists(fol) Then    fso.CreateFolder (fol)Else    MsgBox fol & " already exists!", vbExclamation, "Folder Exists"End IfEnd Sub

yogin · Oct 16th 2012