Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Questions and a problem: The questions are, do you want to copy all of the rows from the "data to copy & paste" to the appropriate areas of the "New Worksheet," or do you want to copy just one particular company. If just one company, how will the code know which one? what do you want the code to do if there is not a matching company number on the "New Worksheet?" The problem is that the code in the workbook you uploaded is password protected, so we can't test the code to do what you want.
    Legare Coleman

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Copy & Paste based on Variable (Excel 2002)

    Hello!
    I need help as usual... my search in this forum found a few posts that may be similar, but because I'm not up on the code (yet!) I can't tell if it will do what I need. I've attached a worksheet as an example... what I'm in need of doing is to transfer a large amount of data from one worksheet to another based on a variable. For example, there may be 10 lines out of 150 lines that need to be copied over to the next worksheet, however they need to go in a certain place in the new worksheet. The variable is the company number which is built into our department number... for example the company number is 75 and the data that needs to be copied has a department number of 75R&D. I'd love a macro that could search the data and find all lines that have a "75" in the left 2 characters, and then copy and paste those lines into the next worksheet... BUT it has to paste it in a spot where company 75's lines need to be. Basically I have about 10 company numbers, which means I'll have 10 areas on the new worksheet where the 10 separate sets of data need to be pasted... man, I hope my spreadsheet makes more sense?!?!?! Any help on this would be greatly appreciated!!
    THANKS!
    Lana

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Hi Legare,
    Sorry about that... for example purposes I had generalized a file that had a macro in it that someone else wrote, therefore the password. It wasn't anything I wrote, so it had nothing to do with my copy & paste dilemma. I copied my example to a "clean" workbook and re-attached to the original question.
    To answer the question in your reply, I would like to copy ALL of the rows from the "data to copy & paste" to the appropriate areas of the "New Worksheet". One thing to keep in mind is that the data in the "data to copy & paste" worksheet may be 150 lines one month and 250 lines long the next, so it varies. I planned on setting up the 10 journal entry templates in the "New Worksheet" with plenty of space between each to accomodate the copying & pasting of the varying lengths of data from the "data to copy & paste" worksheet.
    To answer your next question, there will always be a journal entry template for EVERY company, so we shouldn't have a problem there. Also keep in mind that the first two digits (Left,2) in the department code (in the example 75R&D) is the company number, so I thought we could read the left two digits to determine which got moved and to where?!?! Does that make sense?
    Thanks!
    Lana

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Does this do what you want?

    <code>
    Public Sub CopyData()
    Dim oSrc As Worksheet, oDst As Worksheet
    Dim I As Long, J As Long, K As Long, lDMax As Long, lSMax As Long
    Dim strCpy As String
    Set oSrc = Worksheets("data to copy & paste")
    Set oDst = Worksheets("New Worksheet")
    lDMax = oDst.Range("A65536").End(xlUp).Row - 1
    lSMax = oSrc.Range("A65536").End(xlUp).Row - 1
    For I = 0 To lDMax
    If oDst.Range("B1").Offset(I, 0).Value = "Financial Division" Then
    strCpy = oDst.Range("B1").Offset(I + 1, 0).Value
    K = 0
    For J = 0 To lSMax
    If oSrc.Range("A1").Offset(J, 0).Value <> "" Then
    If Left(oSrc.Range("A1").Offset(J, 0).Value, 2) = strCpy Then
    oDst.Range("A1").Offset(I + 4 + K).Value = oSrc.Range("A1").Offset(J, 0).Value
    oDst.Range("B1").Offset(I + 4 + K).Value = oSrc.Range("A1").Offset(J, 1).Value
    oDst.Range("K1").Offset(I + 4 + K).Value = oSrc.Range("A1").Offset(J, 2).Value
    K = K + 1
    End If
    End If
    Next J
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    There is a reason you are a MVP... this is fantastic!
    Thank you Legare!
    Lana

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Hi Legare... you were so kind to help me out with a great macro, and now I'm changing my mind on what I want it to do... I'm hoping to tweak it a bit further to automate it even more. Rest assured, I've learned so much from you and Hans in regards to Macros, however I'm unable (YET) to actually write the code, just a bit of tweaking at this point. Anyway, below is what I'd like to change around on the original macro that you had written for me... I'm hoping it is doable (if that is a word)... these days, I'm pretty confident that almost anything is possible as you guys & gals on Woody's Lounge are brilliant!! Anyway, here is my wish list...

    Please refer to the attached document...

    *I'd like to copy & paste A7 and B7 to the New Worksheet A15 and B15
    *The narrative in D7 to go over to G15 in the New Worksheet
    *The dollar amount in cell C7 to go over to the New Worksheet K15

    Then it would scan down and recognize A10 is also company 75 (as the first two digits of the Unit are the company number) and need to go over to the new worksheet on the line below the one mentioned above.

    Then it would re-scan the data looking for the next incremental number (83 in this case), and copy all of the data in this worksheet over to a NEW journal entry template in the "New Worksheet" following the same concept in paragraph one above.

    Legare has already created a macro to do this, however I'd like to ADD to it if possible...

    Instead of having a journal entry template for each company already created in the "New Worksheet", once the next company's data is ready to be transferred (in this case company 83), I'd like to have the macro copy the "New Worksheet" range A11 thru I15 (as row 15 will have formula's in it) and place these copied cells 3 rows AFTER the last row of the previous company's data (in this case after company 75's data).

    Of Course then since row 15 was copied (due to the need of formulas in a few of those cells) and now contain some unit, nature, amount, & narrative data that pertain to the previous company number (A24,B24,&G24 in this case) need to be "cleared out" so that company 83's data can be copied over to these cells.

    To complicate it, I do not want any of the zero dollar amount lines to be copied over to the "New Worksheet"... they should just be ignored.


    As always... thanks for the help!!
    Lana

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    I read your post a little earlier this morning and I was hoping to have time to respond. Unfortunately, I am preparing to leave town for two weeks of vacation and, after taking a look, your request is going to take a little more time than I am going to have available before I leave. Hopefully, someone else will jump in and give you some help. If you don't get what you need, give me another post after July 9th and I will see what I can do.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Try the attached version.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Thanks Hans... this works great! You'll be proud to know that I am learning something from you!! I changed the code line below to be "not equal" to zero as opposed to "greater than" zero as some of the amounts will be a credit value, thus be less than zero... I neglected to include that tidbit in my wish list... anyway, I was totally impressed with myself that my change actually worked... soon I hope to have just a sliver of the knowledge you possess in regards to VBA and Excel. You are truly talented. Thanks again Hans!
    Lana
    If wshSource.Range("C" & lngSourceRow) <> 0 Then

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Hi Hans,
    I'd like to change the code to copy and paste "values" as opposed to just paste... the data I'm copying has formula's as it's read from a pivot table for summarization purposes, so the current macro is sorting the columns and then the formula's get copied to the target worksheet. In looking at the code I see a "copy destination" which I'm assuming is the "paste" part, but as you know I'm not trained on all the commands yet so I'm unsure how to get it to "paste values" instead. Of course, I see in the code that there are two copy destination functions, and I only want to change the movement of the Source data to the Target worksheet. The copying of A11:I14 in the target worksheet works fine, so no need to change that part of the code to "paste values". If you could explain breifly in words what it is doing that would be great as well... I've studied it numerous times and I've caught on to part of it, but the paste part has eluded me.
    Thanks as always!
    Lana

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    One way would be to use Paste Special with the option to paste only values, but it is even simpler to set (the value of) the target cell to (the value of) the source cell: change the lines

    wshSource.Range("A" & lngSourceRow).Copy Destination:=wshTarget.Range("A" & lngTargetRow)
    wshSource.Range("B" & lngSourceRow).Copy Destination:=wshTarget.Range("B" & lngTargetRow)
    wshSource.Range("C" & lngSourceRow).Copy Destination:=wshTarget.Range("K" & lngTargetRow)
    wshSource.Range("D" & lngSourceRow).Copy Destination:=wshTarget.Range("G" & lngTargetRow)

    to

    wshTarget.Range("A" & lngTargetRow) = wshSource.Range("A" & lngSourceRow)
    wshTarget.Range("B" & lngTargetRow) = wshSource.Range("B" & lngSourceRow)
    wshTarget.Range("K" & lngTargetRow) = wshSource.Range("C" & lngSourceRow)
    wshTarget.Range("G" & lngTargetRow) = wshSource.Range("D" & lngSourceRow)

    Just for educational purposes: the code to Paste Special is a bit longer - you have to use separate instructions to copy and to paste:

    wshSource.Range("A" & lngSourceRow).Copy
    wshTarget.Range("A" & lngTargetRow).PasteSpecial Paste:=xlPasteValues
    wshSource.Range("B" & lngSourceRow).Copy
    wshTarget.Range("B" & lngTargetRow).PasteSpecial Paste:=xlPasteValues
    wshSource.Range("C" & lngSourceRow).Copy
    wshTarget.Range("K" & lngTargetRow).PasteSpecial Paste:=xlPasteValues
    wshSource.Range("D" & lngSourceRow).Copy
    wshTarget.Range("G" & lngTargetRow).PasteSpecial Paste:=xlPasteValues

  12. #12
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Hi Hans... of course the code works, but my concept doesn't, as the sort causes a problem with the data due to the formulas I have, therefore causing the data to be copied to the target worksheet incorrectly... anyway, I've resorted to recording the following macro

    Columns("F:I").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub


    to copy and paste values of the "source" data, then the sort and subsequent copying & pasting into the "target" worksheet won't be a problem... I'm getting frustrated because I tried to copy the recorded macro and input it into your code and obviously putting it in the wrong place as it keeps giving me the "debug" error. Where / how can I place this recorded code into your code... it needs to be the first thing that happens... prior to the sort.
    As another option, I also tried to "call" the recorded macro AND "call" your code via ONE command button, but apparently I'm only allowed ONE call per button??? Is that right?
    Thanks for the help!
    Lana

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Below the instruction

    Set wshSource = ...

    insert the lines

    wshSource.Columns("F:I").Copy
    wshSource.Range("A1").PasteSpecial Paste:=xlPasteValues

    A button can execute only one macro, but that macro can call other macros if necessary:

    Sub ButtonClick()
    Call Macro1
    Call Macro2
    Call Macro3
    End Sub

  14. #14
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Hi Hans,
    That worked... now the movement of the data to the target worksheet isn't going over because "blank" rows are being sorted and are ending up at the top of the sort. Can the sort eliminate blank rows (reverse the sort order???, ascending vs. descending??)
    Thanks,
    Lana

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy & Paste based on Variable (Excel 2002)

    Are you sure they are real blanks? Excel always sorts blank values last, whether you sort ascending or descending. Spaces, however, come after numbers but before all text when sorting ascending, and after text but before numbers when sorting descending.

Page 1 of 2 12 LastLast

Posting Permissions

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