Copying data to next blank cell in other workbook

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Copying data to next blank cell in other workbook

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Copying data to next blank cell in other workbook

     
    Hello,

    Can anyone help me please with the following:

    I need to take the results from one workbook and write a macro that opens up another workbook, finds the next blank cell in a column and pastes the data at that point.

    I would be really grateful if someone could help me answer this please or even write me the necessary VBA - thank you so much in advance!

    The data I need to copy is this:

    Apologies, I'm not sure yet how to post a proper-looking version so here goes:

    ABCDEFG
    1Agent nameEvaluatorDateScore PercentageFeedbackImprovement PlanFollow up action
    2Jane DoeJoe Bloggs08/08/201198%xyzxyzxyz


    So what I want to do is use a macro to copy A2:G2 to another workbook which looks like this:

    ABCDEFG
    1Agent nameEvaluatorDateScore PercentageFeedbackImprovement PlanFollow up action
    2Jane DoeJoe Bloggs08/08/201198%xyzxyzxyz
    3Bill JenkinsJoe Bloggs09/08/201187%xyzxyzxyz
    4Dave SmithTerry Wade10/08/2011100%xyzxyzxyz
    5John CrossJerry Seinfeld11/08/201142%xyzxyzxyz
    6← Next data line to be inserted here


    I'd really appreciate your help on this please and many many thanks for your time in advance - I know you do this voluntarily and that's really appreciated!

    Brian

  2. #2
    New Member
    Join Date
    Sep 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

    Quick update -

    Crucially it's the 'other workbook' that has foxed me - I've seen other posts about copying and pasting to another sheet but I need to paste to another workkbook entirely and I'm not sure of the VBA for that (or indeed any of it!)

    Many thanks indeed in advance

    Brian

  3. #3
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    6,262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

    Brian adjust the workbook and sheet names, and look at the IF statement as well

    Sub cpy()
    Dim LR As Long, i As Long
    With Sheets("Sheet1")
    LR = .Range("K" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
    If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Workbooks.Open("M:\Access Files\tblStaff Import.xls").Sheets("Staff").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    End With
    End Sub
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2016
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  4. #4
    New Member
    Join Date
    Sep 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

    Thank you so much Trevor - I can see this is going to work as long as I can work out the tweaks for it to suit my workbook - now if I tell you the range I want to copy from and the range I need to paste to, are you able please to tweak the VBA to reflect that? Sorry, I'm not sure where to start on that...

    So the range I want to copy from is A73:G73 and the range I need to paste to in my new workbook is basically the next available row starting at col A

    Sorry for the extra steps but I'm a VBA novice

    Many thanks in advance and thank you for your help so far!

    Brian



    I've updated the file path with the workbook I need to paste to, so the VBS stands at this so far:

    Sub cpy()
    Dim LR As Long, i As Long
    With Sheets("Sheet1")
    LR = .Range("K" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
    If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Workbooks.Open("O:\CMC\Multimedia\Monitoring Sessions Multimedia\2011-12\Multimedia Call monitoring DATA.xlsm").Sheets("Staff").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    End With
    End Sub
    Last edited by bjbeamish; Aug 8th, 2011 at 09:02 AM.

  5. #5
    New Member
    Join Date
    Sep 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

    So far I've updated the file path of the workbook I need to paste to, so the VBA stands at this:

    Sub cpy()
    Dim LR As Long, i As Long
    With Sheets("Sheet1")
    LR = .Range("K" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
    If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Workbooks.Open("O:\CMC\Multimedia\Monitoring Sessions Multimedia\2011-12\Multimedia Call monitoring DATA.xlsm").Sheets("Staff").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    End With
    End Sub

  6. #6
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    6,262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

    Try this

    Sub copyrange()
    Sheets("Sheet1").Range("A73:G73").Copy
    Workbooks.Open ("O:\CMC\Multimedia\Monitoring Sessions Multimedia\2011-12\Multimedia Call monitoring DATA.xlsm")
    Sheets("Staff").Activate
    Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
    ActiveCell.PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End Sub
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2016
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  7. #7
    New Member
    Join Date
    Sep 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: Copying data to next blank cell in other workbook



    Trevor you're a wizard! Thank you so much for putting in the effort to answer my query and you resolved it perfectly!

    I really appreciate your time and your brain!

    All the best

    Bri

  8. #8
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    6,262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

    Pleased to read it helped you Bri
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2016
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  9. #9
    New Member
    Join Date
    Nov 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

    Quote Originally Posted by Trevor G View Post
    Pleased to read it helped you Bri
    Hi! I am new in this world of BVA, found this page, i am trying to adaptate it to the problem i have but doesn´t work. please, which would be the code if instead of copying a73:g73 we had to copy un indetermined name of rows, variable each day?

    when i run this subroutine it tells me "Produced error '9' in execution time. subidex out of interval "

    I know the matter is to select only the rows with information, and main lines of this program are useful... But, sure because of my lack of experience, i am not able to modificate it to make it work...

    Thankyou very much for your time, hope you could help me a bit...

  10. #10
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    6,262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying data to next blank cell in other workbook

      
    Welcome to the forum

    If you need to copy a whole table which changes size then you can look to use CurrentRegion like this

    Range("A3").CurrentRegion.Copy

    Then the rest of the code.
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2016
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

User Tag List

Tags for this Thread

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