+ Reply to Thread
Results 1 to 10 of 10

Find Value to copy all rows with existing vba macro

  1. #1
    Registered User
    Join Date
    05-19-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    18

    Find Value to copy all rows with existing vba macro

    Hi @all

    I have one script:

    Please Login or Register  to view this content.
    This script copy all values from "Tabelle2" - Range N to a new sheet "Tabelle1", but now I want one compare in Tabelle2 before copying the values to a new sheet. Tabelle2 includes also Range A with months (Jan/Feb etc.). So I need my script with more extension u know to ask for the actual month (compare with windows maybe), and copying only the values where the month is same like the actual month.

    I hope u'll understand my requirements

    thx
    Last edited by lucascaletta; 07-28-2014 at 06:25 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,380

    Re: Find Value to copy all rows with existing vba macro

    This will copy the visible cells of column N after filtering the dates in column A based on month number.

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    05-19-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Find Value to copy all rows with existing vba macro

    Hi Bernie Deitrick,

    thank u so much, for ur support . Can we extend your script?

    1.
    So your script ask for the month with numbers, is it possible to decline the month numbers for month words?

    for example: 1=Jan, 2=Feb, 3=Mar, 4=Apr, 5=May, 6=Jun, 7=Jul, 8=Aug, 9=Sept, 10=Oct, 11=Nov and 12=Dec

    2.The month are placed in A from A4 to (open end).

    So I've tested the following in ur script, but it copies only one row

    Please Login or Register  to view this content.
    Because in A there are many rows for different months a year and I will know which values are called in N for one month (for example "Jan"). So it should only show all values of Jan or one other month.

    3.
    And if I use your script, it will ask for the month and the next is - it will asking for saving one copy of the "QuellDatei" (test.xlsx) - why? Can we hide that? It looks like, that the "Quelldatei" won't closed with "Workbooks(Quelldatei).Close, with my old script - it works.

    Thanks
    Last edited by lucascaletta; 07-24-2014 at 07:13 AM.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,380

    Re: Find Value to copy all rows with existing vba macro

    I thought you had dates in column A, but if you have words (Jan Feb etc.), try this. It should also prevent the file-close dialog from appearing.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-19-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Find Value to copy all rows with existing vba macro

    Beautiful Bernie,

    now I have the value of one month nice. Now we have to count the values which are found, so that I can see which one is the most used one to create one chart (For that I have one script).

    Is it too difficult to extend ur script to create one chart with the following requirement? One script for creating one chart can be found in my other thread: http://www.excelforum.com/excel-prog...ic-colors.html

    1. if we choose while the first start of ur script the month "Jan" it will create the chart with the values of Jan
    2. if we choose with next start another month (maybe "Mar") - how will it shown in the existing chart?

    So that we can see if the values descending or ascending, u know. The goal is, that not the values in the new sheet are interesting, but the chart Pictures say more than a thousand words

    thanks :-*
    Last edited by lucascaletta; 07-24-2014 at 09:30 AM.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,380

    Re: Find Value to copy all rows with existing vba macro

    Just delete the current chart and add a new one:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-19-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Find Value to copy all rows with existing vba macro

    Bernie,

    u made my day thank u for ur support

    One question, if we create in every sheet one chart for the actual month. How is it possible to create one mainchart in the first sheet (Conclusion) of two month (previous and actual)? This main chart should show a comparison between the months (sheets). So all what I have is now, a sheet for the month (maybe Jan & Feb).

    Jan looks like:

    === A === B ===
    === Car1 === 5 ====
    === Car2 === 2 ====
    === Car4 === 1 ====

    Feb looks like:

    === A === B ===
    === Car1 === 2 ====
    === Car2 === 4 ====
    === Car4 === 5 ====

    Now, all what I need is a mainchart of the value from "Jan" & "Feb" etc., u know. So that we can see which car is more used in both month BUT if there's no previous month it should only showing the selected month.

    Thank u so much
    Last edited by lucascaletta; 07-25-2014 at 06:49 AM.

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,380

    Re: Find Value to copy all rows with existing vba macro

    I would use a Pivot Chart to do that - it allows quick comparisons of tabular data. If you cannot figure out how to do that, post a workbook...

  9. #9
    Registered User
    Join Date
    05-19-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Find Value to copy all rows with existing vba macro

    Thx Bernie, I have found a solution by trial and error ... but it looks like that "xlColumns" is not the best for me.

    I will mark this thread with ur solution, u r the guy of the week for me

    If u have an idea for a better chart type , let me know ...

    thx

  10. #10
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,380

    Re: Find Value to copy all rows with existing vba macro

    xlColumns was what you used in your posted macro - Just decide on what you want, and record a macro with the type of chart you want, then post it here....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to copy rows from the data sheet to existing worksheet
    By terence8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2014, 08:08 AM
  2. Replies: 11
    Last Post: 11-04-2013, 04:32 PM
  3. Replies: 3
    Last Post: 10-14-2013, 03:06 PM
  4. Need macro to find data rows and copy to another worksheet.
    By n2lectual in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2009, 04:07 AM
  5. [SOLVED] How to find the rows existing in one column but not the other?
    By Fred in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1