Posts by IrelandBird00

  • VBA Code to Create new Workbooks and Move Saved Files

    Re: VBA Code to Create new Workbooks and Move Saved Files


    I've got something that I think will work for my needs for the most part but I am running into a compile error when pasting the data onto a new worksheet.



    It errors here:

    Code
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False


    I am getting error message Compile error: Expected: end of statement and it is highlighting the word "Paste" on the Paste:=xlPasteAllUsingSourceTheme


    I have been googling how to address this but am at a loss. Any ideas for a fix?

  • VBA Code to Create new Workbooks and Move Saved Files

    Hello Ozgrid Forum!


    I am needing someone to show me how to build a VBA code to do all the things I want to do with the attached file (it is false info but will suffice for VBA test needs and to illustrate what I need accomplished). What I need to be able to do is have a VBA code that will:



    1) Split the one TEST Booklet.xlsx worksheet('Sheet1') into multiple WorkBOOKS with specific file saved names in .xlsx format. I need the VBA to look down Column C (Department) and create a new WORKBOOK.xlsx for each unique list of Departments. Meaning that if the Department repeats then it needs to be on the same workbook and when the department changes to a different number, then a new workbook is created listing those departments. I need the header row to remain for each newly created file and in the same format as the original. Also, all corresponding row data needs to be repeated into the newly created workbooks (I don't want to end up with just a list of departments but all the data tied to each department (e.g., Market, Group, Location, Employee Number, etc...))



    2) When the new workbook is created, I need the file saved to "C:\Workbooks" and with a specific file name for each. The file name needs to be Group-Department (example: Row 1 in my attached test file would need to be saved as 11-1103.xlsx. Row 4 should trigger a new workbook creation with a file save name of 12-1204.xlsx)



    3) Once all the files are created and saved as the proper name in the "C:\Workbooks" folder, I need the VBA code to move (Not copy) the files to their final spot based on their Group. Meaning I will have a C:\Workbooks\11 folder, a C:\Workbooks\12 folder, a C:\Workbooks\13 folder, etc... I need the newly created files in the C:\Workbooks folder to be moved into their respective Group folders. So file name 11-1103.xlsx would need to be moved from C:\Workbooks into C:\Workbooks\11 and file name 12-1204.xlsx would need to be moved from C:\Workbooks into C:\Workbooks\12 folder. Make sense?





    I know this is asking a lot without actually showing any work I've done to try to figure this out on my own but I honestly don't know where to begin on this one. If anyone can use the attached test document and make a macro accomplishing my needs, I can adjust it to work with my real data.



    Thanks again for anyone willing to help out!!!

  • Need VBA to Count Rows (Should be simple...I hope)

    Re: Need VBA to Count Rows (Should be simple...I hope)


    Quote from snb;705919
    Code
    Sub M_snb()
    c00 = InputBox("Date to look for", , Date)


    range("V5").Resize(3) = application.transpose(Array(Application.CountIf(Columns(3), CDate(c00)), Application.CountIf(Columns(5), CDate(c00)), Application.CountIf(Columns(7), CDate(c00))))
End Sub



    I could not get this to work. It returned "0" for my data rather than actual counts of the dates.

  • Need VBA to Count Rows (Should be simple...I hope)

    Re: Need VBA to Count Rows (Should be simple...I hope)


    I got it to work by changing my code over to this:


    Code
    Dim vQuestion
    vQuestion = Application.InputBox(Prompt:="Enter Date Needed with Quotation Marks!")
    Range("V5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-5]," & vQuestion & ")"
    Range("V6").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-4]," & vQuestion & ")"
    Range("V7").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3]," & vQuestion & ")"

  • Need VBA to Count Rows (Should be simple...I hope)

    So I have an input box that ask for a date. I enter 03/13/2014 and I want excel VBA to count the number of rows that contain this date in columns Q, R, & S. Below is the code. I know my count piece is off. But I don't know what the correct code should be. Any help is appreciated.


  • InputBox to Delete rows that contain a date greater than the input

    Re: InputBox to Delete rows that contain a date greater than the input


    ok I tried to attach the actual document I am using but it won't upload on this forum. So I put the file (with the names and home addresses changed to protect personal information) online so you can easily retrieve there. Those links are below. Also, the changes to personal information in the file will in no way affect how the macro works.


    Below is the full macro so you can run and see what I see. Adding the CDate piece did not fix the issue :(. When the prompt box appears, I enter "01/25/2014" without the quotation marks of course.


    http://www.filedropper.com/militarylifequery


    NOTE: When you download the file from filedropper, it will automatically add underscores where a space should be in the file name. It shouldn't affect anything in the macro below since there is no reference to an active window but only an active sheet. Just FYI...I guess.


  • InputBox to Delete rows that contain a date greater than the input

    Re: InputBox to Delete rows that contain a date greater than the input


    Here is some more code if it helps. I swear I think my issue is in the delete row part or in the datevalue issue.


    So, just to update, even if my cells are in MM/DD/YYYY format, excel reads that as a DateValue already. So, my prompt for 01/25/2014 shouldn't cause any issue, I would think.


  • InputBox to Delete rows that contain a date greater than the input

    Re: InputBox to Delete rows that contain a date greater than the input


    No harm no foul. :)


    I am very new to VBA so excuse my ignorance in a lot of this.


    No I am not using the function correctly. should be just .Value rather than .DateValue


    I was using Rows(i).EntireRow.delete because that is the code I saw in another forum that someone was able to make work in another VBA code...in a land and time far far away..que starwars scroll. :)


    I believe my main issue is the value I am inputting and how Excel is reading that vs what is actually in my excel sheet. I have read that DateValue in excel changes any MM/DD/YYYY date into a number. However, my excel sheet column "G" has all the values listed as MM/DD/YYYY and not necessarily a DateValue.


    So since VBA input is changing 01/25/2014 to a number (DateValue)...say 401258 and then looking for any DateValue larger than that in a list that contains MM/DD/YYYY formats rather than DateValue formats, it deletes no rows.


    I guess what I need is either a way to change my column "G" into DateValue format and then run the code above then change the format back to a Short Date OR I need to find the proper commands in the code to make my input be read as MM/DD/YYYY and not as a DateValue as I have it listed currently.


    Make sense?

  • InputBox to Delete rows that contain a date greater than the input

    Re: InputBox to Delete rows that contain a date greater than the input


    Well you don't have to be rude about it...



    This code throws no errors but also does not delete any rows after prompting for input. What I am entering is 01/25/2014 in that format.

  • InputBox to Delete rows that contain a date greater than the input

    Hello,


    I am trying to build a macro that will contain a section that will prompt the user for a date (MM/DD/YY) and then look at a column in the current active sheet and DELETE the row if the date is greater than or equal to the input date.


    I will have a header row.


    For the sake of VBA build, let's say that the header row runs from (A1) through (P1) and that the date column is column (G)


    Also the date format in Column (G) is the Short Date format in Excel.


    Everything I've googled and tried does not delete the rows if column (G) contains a date greater than or equal to the input date.


    If you can, make the Dim PED As Date (PED = Period End Date)


    I'm trying to get one built from scratch if I can. Any help is appreciated!


    Thanks!

  • VBA code for changing source workbook name

    Re: VBA code for changing source workbook name


    Quote from patel;688788



    THANK YOU!!!!! Patel you are a life saver! Works perfectly and I can now see how the coding works and how I can utilize that coding/functionality in other macros I have. I can't thank you enough!!

  • VBA code for changing source workbook name

    Re: VBA code for changing source workbook name


    Quote from patel;688755



    OK so doing that makes my code look like this:


    That opens the first email file but then fails because of lines:
    Windows("ASCHR_SCH_I9_INCOMPLETE_RPT_HR.xls").Activate
    &
    Windows("ASCHR_SCH_I9_INCOMPLETE_RPT_HR.xls").Activate


    I need to pull data (copy and paste) from those two files that are open into a third excel file (I-9 Merge Report.xlsx).

  • VBA code for changing source workbook name

    Re: VBA code for changing source workbook name


    Quote from patel;688703

    where are the files likE ASCHR_SCH_I9_EMAIL_LIST.xls ?
    in that directory there is only one file with similar name or more ?
    what about a dialog windows for selecting the right file ?


    The files are saved to my desktop.
    Yes there is only the file ASCHR_SCH_I9_EMAIL_LIST & ASCHR_SCH_I9_INCOMPLETE_RPT_HR on my desktop.
    I do not know how to create/use a dialog window.


    Sorry, my knowledge is beginner at best.

  • VBA code for changing source workbook name

    Re: VBA code for changing source workbook name


    Quote from patel;688616

    try this code



    Let me post the entire VBA code I am using. I know it is simplistic. I need to know how to copy data from both of these workbooks into a third workbook. Where the source workbooks have a changing name due to the numerical value added at the end. I appreciate the code above but I am afraid I am not advanced enough to figure out how to make that work in my VBA code below.


  • VBA code for changing source workbook name

    Hello,


    I run a daily report that uses VBA macros to VLOOKUP data from multiple workbooks. I receive these workbooks via email every morning and they are generated via a PeopleSoft SQL program. So my reports come with a name and then a corresponding numeric value that changes each day the report is ran.


    (i.e. ASCHR_I9_EMAIL-748596 & ASCHR_I9_INCOMP-459987)


    As it stands now, I have to rename and manually remove the numeric values at the end of these files so that my VBA code will work. What I want to know is, is there a way or VBA code that I can use that will only use a certain portion or number of characters of a workbook file name? That way I can just open these files without having to rename them manually. If I can get this part solved, then I can automate the entire process. Below is an excerpt of the code I am using. The two Window names are the result after I have removed the numeric values because they are not static, they change daily.