none
VBA copy data to a range RRS feed

  • Question

  • hi,

    writing code to loop through data tables extracting data and placing it in range("a760:i760") on worksheet 'DATA'.

    need help to take range("a760:i760") and copy it to the next empty cell on worksheet 'ESTIMATE' range ("a91:a600")

    thanks

    Doug

    Thursday, February 16, 2017 10:07 PM

Answers

  • Doug,
    re: details
    You also left off the "+1" when getting the last row.
    The following worked for me...
    '---
    Sub copydata_R1()
    Dim LastRow As Long
    Dim X As Range

    Application.ScreenUpdating = False
    For Each X In Worksheets("Data").Range("A357:A756").Cells
    If X <> "" Then
      Worksheets("Data").Range("A759").Value = X.Offset(0, 0).Value
      Worksheets("Data").Range("B759").Value = X.Offset(0, 1).Value
      Worksheets("Data").Range("C759").Value = X.Offset(0, 2).Value
      Worksheets("Data").Range("D759").Value = X.Offset(0, 3).Value
      Worksheets("Data").Range("H759").Value = X.Offset(0, 7).Value
      Worksheets("Data").Range("I759").Value = X.Offset(0, 8).Value
      LastRow = Worksheets("ESTIMATE").Range("A601").End(xlUp).Row + 1
      Worksheets("DATA").Range("A759:I759").Copy Destination:=Worksheets("ESTIMATE").Cells(LastRow, 1)
    End If
    Next
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)



    Friday, February 17, 2017 2:31 AM

All replies

  • Doug,
    re: copy from sheet to another sheet

    Dim LastRow As Long
    LastRow = Worksheets("ESTIMATE").Range("A601").End(xlUp).Row + 1
    Worksheets("Data").Range("A760:I760").Copy Destination:=Worksheets("ESTIMATE").Cells(LastRow, 1)

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)



    • Edited by James Cone Thursday, February 16, 2017 10:44 PM
    Thursday, February 16, 2017 10:43 PM
  • Jim, thanks for your response but it doesn't seem to work. probably my fault for not explaining well enough or not implementing your code properly. here is what I now have;

    Sub copydata()
    Application.ScreenUpdating = False
    For Each X In Range("A357:A756")
    If X <> "" Then
    Range("a759") = X.Offset(0, 0)
    Range("b759") = X.Offset(0, 1)
    Range("c759") = X.Offset(0, 2)
    Range("d759") = X.Offset(0, 3)
    Range("h759") = X.Offset(0, 7)
    Range("i759") = X.Offset(0, 8)
    Dim LastRow As Long
    LastRow = Worksheets("ESTIMATE").Range("A601").End(xlUp).Row
    Worksheets("DATA").Range("A759:I759").Copy Destination:=Worksheets("ESTIMATE").Cells(LastRow, 1)

    End If
    Next
    End Sub

    the idea being that the first time it loops data placed in range("a91")...next range("a92") ect.

    assuming these ranges are empty!!

    any thoughts?


    • Edited by 6da4 Thursday, February 16, 2017 11:04 PM
    Thursday, February 16, 2017 10:58 PM
  • These two lines...
      Worksheets("DATA").Range("A759:I759").Copy
      Destination:=Worksheets("ESTIMATE").Cells(LastRow, 1)

    Shoujld be one line with a space between them
    -or-
    have a line continuation character between them
    Copy _
    Destination:

    Also, "Dim LastRow as Long" should be at the top of the code.


    • Edited by James Cone Friday, February 17, 2017 12:33 AM
    Friday, February 17, 2017 12:31 AM
  • Hi 6da4,

    you had mentioned that the above solution did not work.

    so please try to confirm that your range (A759:I759) in "Data" worksheet contains the data.

    when you run the code try to debug it and check the worksheet that it copies the data or not.

    you had mentioned that ,"the idea being that the first time it loops data placed in range("a91")...next range("a92") ect."

    I am not sure what you are saying. if I try to see the range in the code mentioned by you then I am not able to find range (a91) or (a92) anywhere in the code.

    you are looping the Range("A357:A756"), how it related with range (a91)?

    for simplicity, I suggest you to try to test the code that only copy the data from one sheet to another.

    when you get clear how it works then you can implement that code in your original code to work together.

    then if you get any error then let us know about that, we will try to solve it.

    below , there is another example to copy the range from one sheet to another.

    Sub sbCopyRangeToAnotherSheet()
    
    'Method 1
    
    Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1")
    
    'Method 2
    
    'Copy the data
    Sheets("Sheet1").Range("A1:B10").Copy
    'Activate the destination worksheet
    Sheets("Sheet2").Activate
    'Select the target range
    Range("E1").Select
    'Paste in the target destination
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    End Sub
     

    Reference:

    Copy Data from one Worksheet to Another in Excel VBA

    3 Ways to Copy and Paste Cells with VBA Macros + Video

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 17, 2017 12:47 AM
    Moderator
  • hi Deepak,

    first let me thank you and James for your help...much appreciated!

    data does go to range("a759:i759") - certain of that.

    what I am trying to do is loop through a range on worksheet("DATA") and place selected data in range ("a759:i759").

    next I want to find the next empty row in range("a91:a600") on worksheet("ESTIMATE") and paste worksheets("Data").range("a759:i759") into that cell

    had some working code that did this before on a laptop that got stolen!


    • Edited by 6da4 Friday, February 17, 2017 1:30 AM clarity
    Friday, February 17, 2017 1:24 AM
  • Doug,
    re: details
    You also left off the "+1" when getting the last row.
    The following worked for me...
    '---
    Sub copydata_R1()
    Dim LastRow As Long
    Dim X As Range

    Application.ScreenUpdating = False
    For Each X In Worksheets("Data").Range("A357:A756").Cells
    If X <> "" Then
      Worksheets("Data").Range("A759").Value = X.Offset(0, 0).Value
      Worksheets("Data").Range("B759").Value = X.Offset(0, 1).Value
      Worksheets("Data").Range("C759").Value = X.Offset(0, 2).Value
      Worksheets("Data").Range("D759").Value = X.Offset(0, 3).Value
      Worksheets("Data").Range("H759").Value = X.Offset(0, 7).Value
      Worksheets("Data").Range("I759").Value = X.Offset(0, 8).Value
      LastRow = Worksheets("ESTIMATE").Range("A601").End(xlUp).Row + 1
      Worksheets("DATA").Range("A759:I759").Copy Destination:=Worksheets("ESTIMATE").Cells(LastRow, 1)
    End If
    Next
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)



    Friday, February 17, 2017 2:31 AM
  • James,

    activated sheet 'DATA', clicked 'view code' and pasted your exact code and assigned it to button control:

    getting a 'sub or function not defined' error

    Doug

    Friday, February 17, 2017 12:45 PM
  • Hi James,

    instead of copy/paste I manually typed in your code and it worked perfectly!!!

    thank so much for your help...kinda new to this but trying to learn. really helps having all you folks out there on the forum.

    Doug

    • Marked as answer by 6da4 Friday, February 17, 2017 1:10 PM
    • Unmarked as answer by 6da4 Friday, February 17, 2017 5:32 PM
    Friday, February 17, 2017 1:10 PM
  • Doug,

    You are welcome;  it is nice to get confirmation of one's effort.

    FWIW... marking the post with the correct code, as the answer, is the normal procedure.

    regards,
    Jim Cone
    Friday, February 17, 2017 5:05 PM
  • went back and marked your code as answer

    again many thanks

    Doug

    Friday, February 17, 2017 5:33 PM