Question on copy/pasting dynamic information

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Question on copy/pasting dynamic information

  1. #1
    Board Regular
    Join Date
    Aug 2003
    Location
    Lisbon
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Question on copy/pasting dynamic information

     
    Hi everyone,

    I have a sheet where I have information that is copied over from an external source in blocks. By this I mean:

    In column A I will have the title of a location;
    in Col B I will have various lines of specs; Column C I will have the Country of the location in column A etc.

    Looks something like this (....... represents blank cells):

    LOCATION1 Spec1 Country1
    .................Spec2............
    .................Spec3............
    LOCATION2 Spec1 Country1
    .................Spec2............
    .................Spec3............

    This repeats for numerous but varying locations every month.

    So I want it to look like this as I need to sort, count locations etc:

    LOCATION1 Spec1 Country1
    LOCATION1 Spec2 Country1
    LOCATION1 Spec3 Country1
    LOCATION2 Spec1 Country2
    LOCATION2 Spec2 Country2
    LOCATION2 Spec3 Country2

    My question is, is it possible to automate the copying of each location and country info in the blank cells with this all being dynamic?

    Thanks again as always,
    Ana
    Last edited by brunette; Jun 19th, 2017 at 06:42 AM. Reason: Monday Morning Typo Central (I need more coffee!)

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Posts
    4,644
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question on copy/pasting dynamic information

    You could try this:

    Highlight your data. Press CTRL-G. Press the Special button. Press the blanks radio button. Then OK. Press = then the up arrow key. Press CTRL-ENTER. Copy and paste special values if required.

  3. #3
    Board Regular
    Join Date
    Aug 2003
    Location
    Lisbon
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question on copy/pasting dynamic information

    I forgot to mention that the information in column B (specs) is also varying. Some months may have more or less entries.

    So, Column A = variable data
    Column B = variable data
    Column C = variable data

    Hope this helps!
    Ana

  4. #4
    Board Regular
    Join Date
    Aug 2003
    Location
    Lisbon
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question on copy/pasting dynamic information

    Quote Originally Posted by steve the fish View Post
    You could try this:

    Highlight your data. Press CTRL-G. Press the Special button. Press the blanks radio button. Then OK. Press = then the up arrow key. Press CTRL-ENTER. Copy and paste special values if required.
    Holy Moly that worked! And I learnt something new that isn't VBA related! Thank you!

  5. #5
    Board Regular
    Join Date
    Aug 2003
    Location
    Lisbon
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question on copy/pasting dynamic information

    Hi again,

    Now I've run into another issue...

    If I try to automate this step by using the following code to select all my (variable) data and apply the above steps, it tells me I have no blank cells. If, however, I select all the (same) data manually, it works.

    Any idea how I can incorporate this into a working macro?

    Code I have used (that is causing error)

    Code:
    Sheets("Sheet1").Select
        Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("A2:E" & LR).SpecialCells(xlCellTypeConstants, 23).Select
        
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "=R[-1]C"
    EDIT:

    Never mind. I figured out my mistake.

    Fixed code:

    Code:
    Sheets("Sheet1").Select
        Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("A2:E" & LR).Select
        
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "=R[-1]C"
    Last edited by brunette; Jun 19th, 2017 at 08:06 AM.

  6. #6
    Board Regular
    Join Date
    Oct 2009
    Posts
    4,644
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question on copy/pasting dynamic information

    When using LR there based on your data you should probably use column B as column A has blanks...so maybe:

    Code:
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("A2:E" & LR).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Last edited by steve the fish; Jun 19th, 2017 at 08:12 AM.

  7. #7
    Board Regular
    Join Date
    Aug 2003
    Location
    Lisbon
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question on copy/pasting dynamic information

      
    Hi Steve,

    As always, you're my lifesaver. I will give that a try since I notice that with my current code it doesn't pick up the data from the last group, so definitely going to give it a whirl. Thanks again!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com