Posts by Royzer

    I am looping through a folder and copying three columns from each file to a single sheet in a single workbook. What I'd like to do is delete and shift up any range of three adjacent cells that are blank within each three column grouping.

    For example, in the attached image I would need to delete A5:C5, A7:C9, E5:G7 and I4:KI4, among others. I cannot delete rows because no row will be completely blank due to the number of columns I will have once all columns have been copied into the sheet.

    I'd really appreciate any help you can give me. Thanks!

    Emails in this inbox contain document-specific information in the subject field and it receives several hundred emails a day. I'd like to be able to create a list of emails in the folder based only on the Subject field every morning for tracking purposes, as the emails are always moved to a different folder during the day. Is this possible?

    Thank you.

    Re: Programmatically adding code to ThisWorkbook

    That is a really good idea. But something I don't understand is happening. I created a new workbook, selected Sheet1 (Sheet1) in Project Explorer, then set the Object drop-down box to Worksheet and the Procedure drop-down to BeforeDoubleClick. Then I dropped in the rest of the code, saved the file, and reopened it.

    The strange thing to me is that it now inserts two rows with a double-click, whereas the SheetBeforeDoubleClick in ThisWorkbook of the master file only inserts one row. It also appears that the copy and paste that the ThisWorkbook version does is not working with the new way.

    Here's the code:

    Re: Programmatically adding code to ThisWorkbook

    Quote from S O;779014

    Hi Royzer,

    It's certainly something that can be done, but it requires you to allow programmatic access to the VBProject object model which can lead to a world of security vulnerabilities (which is why it's disabled by default). A simpler way would be to have a template workbook with the code already store and just write the data to that instead of a new workbook and then "Save As".

    Just to make sure that I follow:

    I would do a one-time-only set up of a workbook for each of the sheets I will split out and put the ThisWorkbook code in each of them. Then, each month run a macro to copy the data from each of the sheets in the master workbook and paste it to the corresponding files, right? Because I need to split the sheets out of the master into twenty or so workbooks each month, but the sheets will have the same names each month, as will the receiving workbooks.

    If that is the case, I think the only help I would need then is a way to make sure the data from each sheet in the master workbook goes into the correct existing workbook/template (which would have the same name as the sheet in the master file). For example, columns A:C in the sheet named "KOLN" in the master workbook would paste into the separate workbook named "KOLN". (The data range would always be columns A:C).


    I have a workbook with 20+ sheets that I am going to split into individual files using VBA. The workbook has some code in ThisWorkbook that inserts rows by double-clicking where you want to insert them. This needs to be added to each workbook that is created when the file is split and saved. I've searched for answers quite a bit and am baffled as to how to make it work. I would really appreciate any help you can give me.
    The code I would like to add to ThisWorkbook in the newly created files is:

    This is the code I am using to split the file:

    Thanks again!

    I have worksheets named 1 through 21 that are pulling data from several other worksheets in the same file. I need a way to copy A1:C58 of each of these numbered sheets (but only these) and paste the ranges vertically on an existing sheet named "Save As DIF".

    I've searched for this, but everything I've seen has been copying every sheet in the workbook. Thanks!

    Re: VBA to match value in one column to another and paste adjacent value

    I've attached a copy of the original file with the results of having run the most recent code. I've highlighted the stations in column O and P to show where there were two instances of station amounts but only the first one pasted to column G.

    Re: VBA to match value in one column to another and paste adjacent value

    Runtime error '91'
    Object Variable or With block variable not set

    It gave me this error when it got to WYMT in column B. There is no deposit for WYMT this particular day, so is not listed in column O. I wonder if that is an issue?

    When I pressed Debug this is the line that was highlighted:

    If ((Not F Is Nothing) And (Cells(F.Row, CCCol2) <> "")) Then

    Re: VBA to match value in one column to another and paste adjacent value

    Quote from PCI;772362

    Take care today macro does not add the value from column "P" to column "G", it replaces the value and today column "G" is empty:
    Do we need to add column "G" with column "P" value ?

    The code you just gave me is fantastic. Yes, it would be wonderful if there was a way to add the second instance of an amount to the first in column G, but the result would always have to show the individual amounts in the cell with a "+" between them, i.e. "=2500+1540" instead of the total of 4,040. If that's not possible, no problem. I am grateful for what you have given me.

    Re: VBA to match value in one column to another and paste adjacent value

    Quote from PCI;772358

    "If the station in col O matches the station in col B, paste the adjacent value from column P into the corresponding cell in col G."
    Do we need to clear the value in column P when it has been added into column G ?

    I ran the code and it works great. It would be awesome if the value in column P was cleared after it was added to column G, as I could immediately see which amounts were left for me to enter manually. Otherwise, I will need to review the paper version of the document and cross off the ones that placed in order to determine which ones I need to enter manually.

    Re: VBA to match value in one column to another and paste adjacent value

    Each day I begin by pasting two columns of data in columns O and B. I'm just trying to get the data in column O over to column G by matching the station call letters. I realize that if one station has two entries in column O on a particular day, that only one of them with go into column G. I will have to later edit the cell in col G to add the amount that did not go when the macro ran.

    You gave me this code a couple of months ago that compares stations and amounts to a list of station and amounts, then copies any amounts that match both criteria to specific column/row. What I hoped for this time was to just compare col O to col B and when they match, copy the adjacent value from column P to column G. Perhaps it would just be a case of modifying this code?

    Re: VBA to match value in one column to another and paste adjacent value

    Quote from PCI;772134

    What about a formula like
    G6 = IF(O6=B6,P6,"")

    Most days I will have multiple entries for some stations. I'm just trying to get the first one in without keying it and follow up by keying any other amounts for that station that day. I can't use a formula (like SUMIF), as the cells need to show what the values are composed of so the users who take it from there can see the amounts of the individual transactions (=2500.25+1612.00+53.50) for the work they have to do after I fill it in.