Posts by jvalente

    Hi Guys,


    I'm writing code in a template and I want the user to basically get prompted when the data is already formatted in my template sheet. If it is present (ie the formatted sheet tab is there) the user will be prompted to delete all formatted tabs.



    The code is as follows:



    The only issue is that I get the popup box three times. I only want it to pop up once for any of those instances. If it finds one of them, I have an option to delete all sheets that it finds.


    Hope this makes sense.

    Hi all,


    I've written code that does exactly what I want it to do. I have a drop down that when I select a particular field it will run a particular code ive written (by Val target code within the sheet).


    The code runs fine, however, I would like to once I select a particular field from the drop down open up to search for the file in "my computer", the user can then manually search for the file and then open it, then VBA could take the necessary information from that spreadsheet and then run my code.


    At the moment, I am manually copying and pasting the data from the source spreadsheet to my macro template. This is the manual process I want to automate.


    any clues on where I can find this code or if someone has a good example?


    thanks all

    Hi All,


    I've got a macro to run different code depending on the drop down you select. I'm running into problems now because I've got too many nested conditions (out of stack space (error 28)).


    My code is as below:



    The code is all within one sheet. The above works, however, when I run more conditions it gives me the stack error.


    Any help much appreciated! Thanks

    Re: Copy from Excel to Word VBA


    It's a workplace document that needs to be updated and revised. Firstly it was in excel because we could easily do automation and data retention through excel.


    However, we came across a problem with revision marking, if someone edited the document, how would we know it's been changed. The inbuilt "mark changes" in excel isn't the best and it's a lot better within word. Thus the need to move the sheets to word

    Hi Guys,


    I am writing code to copy sheets in my workbook to word.


    The issue I'm having is how to copy all my sheets and not just the one sheet. The sheet 2.1 is one of them, they go from 2.1 to 2.8 and have similar ranges. Should it be a loop for sheets?


    The other issue is that the table I'm pasting is a bit off centre.


    My code is:


    Hi Guys,


    I have a print macro code that does what I want it do essentially. However, it seems to print the first page by itself and then the following pages after.


    This is evident when you print it to PDF. I have to then combine the two PDFs into one.


    Any ideas why this might be occurring?


    Code
    Sheets(Array("Cover", "Contents", "1. Executive Summary", "1.1 Scope", "1.2 Score & Level", "2.1", "2.2", "2.3", "2.4", "2.5", "2.6", _
        "2.7", "2.8", "3. Next Steps", "4. Contacts")).Select
        Sheets("Cover").Activate
        ActiveWindow.SelectedSheets.PrintPreview
        Sheets("Home").Select

    Hi Guys,


    I'm preparing a report that will have a dynamic table. I will continually add data to the report and it will contain dates. As I run iterations of the report, I'll add more data with more dates and some of these dates will be duplicates.


    I've prepared this formula which I put into name manager:


    =OFFSET('Sheet 1'!$B$6,,,COUNTA(Table1[[#All],[End Date]]))


    Because as I add more data, I want to capture the extra dates. Then I've added that to a drop down list under data validation, however, the duplicated dates show which I don't want...


    Anyone got an idea of removing the duplicates in a dynamic table using a formula?


    Thanks again

    Re: VBA Loop copy and paste based on value of cell and if blank stop


    Hi Jindon,


    That didn't work.


    I found some code and amended it and added in a bit of my own (not elegant I know). But it works. However, I need to make similar iterations for sheets 2.4-2.8 (repeating below). However, this may not be the best way of doing it


    Hi All,


    Wondering if I can get some assistance from the VBA gurus here:


    I want a copy and paste loop essentially.


    I have multiple sheets (named '2.'1 and '2.2') with Rows 'S' which can either be "Y" or "N" (for yes or no). I want to copy the adjacent cell value from Row 'R' when the cell value in S is "Y". I don't want to copy the data in R, when the adjacent cell value is "N" or blank


    Then I want the data to paste into another worksheet (questions) in the first free cell from C8 downwards


    To recap: I want it to loop until it finds and copies data in row R from column S showing "Y" from 2.1 to 2.8 (2.1, 2.2, 2.3 etc) and pastes to the other worksheet (questions) and then stop when all the "Y" are copied and when a blank is encountered and ignoring the N.


    I've attached an example sheet which captures 2.1 to 2.2 sheets to questions as an example.


    Thanks guys

    Re: Create folder macro on cell and save excel file to newly created folder


    Sorry about that!


    N2 references the month you are generating the report.


    In

    Code
    N1 =today()


    Then in

    Code
    N2 =TEXT(N1,"MMMM")


    Giving the result November


    In December when you run the report N2 will give you December and then the macro above will create a new folder for December, and now I want to save the excel as December in the December folder, etc ect

    Re: Create folder macro on cell and save excel file to newly created folder


    Quote from iwrk4dedpr;731497

    Record a macro saving to the new folder. then just append it to your code.


    The problem with that method, I foresee is that the macro recorder will record saving an excel sheet to the newly created folder for that month. Next month it will save the excel to the same folder.


    This report gets generated monthly, so I want it to save to the created folder for that month and then next month for that month and so on.


    Hope that makes sense.


    PS: I forgot to mention that cell (N2) references this month. So the macro above will generate a new folder based on the month referenced in that cell...

    Hi Guys,


    Love this forum. People are very helpful and kind!


    Got another one for you.


    I want to generate reports monthly, and then get a macro to create a folder based on the report I'm creating (for eg make a November folder when reporting on November).


    I found this code online and edited it for my needs and it does create the folder part based on my cell value within my sheet (N2). Only problem is now I want to create a copy of my excel into that newly created folder with the name based on the cell I selected as per below (N2). It only creates the folder now I need code to save the excel there.


    Hi Guys,


    Really simple one here.


    I'm trying to create IF statements where a different macro is run when referencing two cells in the same sheet.


    Basically I have at the moment as a test macro:



    cells N5 and O5 are the same, I've even written the values myself. I want it to be a word, like "october" for example.


    But I keep getting "no" back as a response, even though they are the same value and I should be getting "yes"


    Any idea why this is happening?