Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Copying data to next blank cell in other workbook

This is a discussion on Copying data to next blank cell in other workbook within the Excel Questions forums, part of the Question Forums category; Hello, Can anyone help me please with the following: I need to take the results from one workbook and write ...

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    24

    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

    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
    5,782

    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 2013
    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

    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

    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
    5,782

    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 2013
    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

    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
    5,782

    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 2013
    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

    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
    5,782

    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 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

Page 1 of 2 12 LastLast

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