+ Reply to Thread
Results 1 to 9 of 9

VBA Paste Specific Data To Specific Rows On Multiple Sheets

  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Paste Specific Data To Specific Rows On Multiple Sheets

    Hi, I wonder whether someone may be able to help me please.

    I'm using the code below to extract data from a master sheet ("All Data") and paste relevant data into sheets whose name match a value within a given column set here in the code: Set ws = Sheets(rng.Value)

    Please Login or Register  to view this content.
    Trying to keep this post as simple as possible, the script does two jobs: The following section searches for four specific sets of information which match a given set of criteria, namely "TM - DIR", "Enhancements", "TM - IND" and "TM - OVH".

    Please Login or Register  to view this content.
    The next section pastes the data onto the 'Destination' sheets and it's this I'm having difficulty with

    Please Login or Register  to view this content.
    I'm trying to write the code so that when the piece of data which matches the first search i.e. for the value "TM - DIR" I would like to paste the results on row 8, for the data which matches the second search i.e. for the value of "Enhancements" paste on row 9 and so on for the remaining values, but I receive a 'Subscript out of range' error'.

    I've tried to correct this over the last few days without success.

    I just wondered whether someone may be able to look at this please and offer some guidance on how I can resolve this.

    For ease I've attached a sample file. To run and see the error, please click the 'Launch' button.

    Many thanks and kind regards

    Chris
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    Stepping through the debugger reveals that since ws is set to Corporate Services, with your code:
    Please Login or Register  to view this content.
    you are loading the heading row of the Corporate Services sheet (Apr 14 : Mar 15) into the 1st dimension (1st row) of "a". The line it blows up on is looking at the 8th dimension, which has no value.
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    Hi @natefarm, thank you for taking the time to come back to me with this.

    I can confirm that 'ws' is not set to "Corporate Services". This happens to be the first value on the "All Data" sheet and so loops through this first.

    I do however appreciate that you may think this because if you initially run the code, the script correctly creates all the sheets and extracts the data. If you then step into the code, as you say the script just loops on the "Corporate Services" value. This is because the script above is part of this routine "Sub CreateNewSheets", so when the sheets and headers are created, the script I'm having trouble with then inserts the data extracted from the data sheet.

    This may or may not be the correct way to do this, but with my limited knowledge it was the only way I could get this to work, but I'm more than happy be shown different, perhaps more efficient ways of doing this.

    Many thanks and kind regards

    Chris

  4. #4
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    I'm just going by what the debugger shows me when I hit Launch and then click Debug. See attached screen print. At this point in the code, the value of i is 13, but there is only 1 level in a.

    Debug.PNG

  5. #5
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    Hi @natefarm, thank you for coming back to me with this. I fully appreciate what you're saying, but the issue I have is not pasting the values to the correct worksheet, this piece of the code works fine. The problem I have is trying to paste the extracted data to the different rows on each sheet so when this piece of code is run:

    Please Login or Register  to view this content.
    I'd like the data to be paste in row 8, and when this piece of the code runs:

    Please Login or Register  to view this content.
    I'd like the data to be paste in row 9.

    I hope this helps.

    Many thanks and kind regards

  6. #6
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    So are you saying you are no longer getting the 'Subscript out of range' error you reported in your original post? If not, then the problem remains as I had described.

    Let's look at some issues. First with the following code:
    Please Login or Register  to view this content.
    The separate If statements are meaningless because the resulting process in each is exactly the same. You would get the same results with:
    Please Login or Register  to view this content.
    Since you said you want the results of the four text values to be pasted to separate lines, I assume that the above is not what you want.

    Personally (and I mean this with all due respect to the code writer), I think the code is more complicated than necessary. The simplest approach is usually the best (easier to understand and maintain, and more efficient). My understanding of what you are trying to do (I may be wrong) is to add the Forecast (FTE) numbers in All Data to the appropriate monthly column in the sheet matching the Portfolio name, and in rows 8, 9, 10, or 11, based on the Project Name. Based on that, please try the following and modify as needed:

    Please Login or Register  to view this content.
    Similar things could be done with the other two subroutines if you wish. I hope this is close to what you need.
    Last edited by natefarm; 09-12-2014 at 01:42 PM. Reason: Additional comments requested by poster

  7. #7
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    Hi @natefarm, thank you very much for coming back to me with this, and my apologies for not being particular with the problem.

    What I was trying to do for each 'If Statement' i.e.

    Please Login or Register  to view this content.
    Was paste the extracted data into a set row on each sheet.

    What I had tried to do with my limited knowledge was to attempt to do this via this piece of code:

    Please Login or Register  to view this content.
    But as I indicated I had fallen at the first hurdle and received the error message and it was this last section of code that I was looking for help with, again my apologies for not making this clear.

    However, having said all of that the 'Simplified' script you kindly provided works perfectly, thank you so much!!

    So that I can learn from this, may I ask, could you add some comments to the code.

    Many thanks and kind regards

    Chris

  8. #8
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    Some were there already, but I've added a few more. Glad I could help.

  9. #9
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Paste Specific Data To Specific Rows On Multiple Sheets

    Hi @natefarm, that's great.

    All the best and kind regards

    Chris

+ 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. Combineing specific rows from multiple sheets to one sheet
    By Muttly in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 02-14-2013, 10:17 AM
  2. Need to copy ranges from multiple sheets then paste to sheet under specific headers
    By the duke in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2013, 12:17 AM
  3. Cut and paste rows which have a specific value in a specific column
    By Enigma1985 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-19-2012, 03:46 AM
  4. [SOLVED] How to delete rows with specific value for multiple sheets
    By cnj_zeng in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-14-2012, 04:29 PM
  5. Identify specific rows based on a value across multiple sheets, and paste into new
    By optimisticsam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2011, 10:02 AM

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