none
Data Transfer Issue (Run-time error '1004': Application-defined or object-defined error) RRS feed

  • Question

  • I am trying to transfer data from one sheet to another using a macro. Data will be transferred as long as there is a number in the columns being examined. In the second sheet, I would like the data to be separated by one column so I can do calculations in the blanks with another macro. However I keep getting a run time error if I use cells as a range instead of a column. My code is as follows:

    Sub wtf()

    Dim i As Double, n As Double

    i = 5

    n = 4

    Sheets("Boys").Range("B1:D1000").Copy Destination:=Sheets("Boys Data").Range("A1")

        Do Until Sheets("Boys").Cells(2, i) <= 0

               Sheets("Boys").Range(Cells(1, i), Cells(1000, i)).Copy Destination:=Sheets("Boys Data").Cells(0, n)

        i = i + 1

        n = n + 2

        

        Loop

    End Sub

    The code works if end it with .Range("D1") or another column, but I can't find a method to create a space in-between columns use that. I can send the excel document if necessary. Any help is appreciated!

    Thursday, September 17, 2015 7:24 PM

All replies

  • In the Destination you have cells(0, n)  (ie. cells(zero, n).  Rows commence at 1 not zero. However, the following code tests how much data is in each columns and only copies the data without additional empty rows.

    Note that the space and underscore at the end of the Copy line is a line break in an otherwise single line of code. I use them when posting here so the lines don't break where not wanted.

    Sub wtf()

        Dim i As Long, n As Long    'Changed to long by OssieMac
        i = 5
        n = 4
       
        With Worksheets("Boys")
            .Range(.Cells(1, "B"), .Cells(.Rows.Count, "D").End(xlUp)).Copy _
                            Destination:=Sheets("Boys Data").Range("A1")
        End With
       
        Do Until Sheets("Boys").Cells(2, i) <= 0
            With Worksheets("Boys")
                .Range(.Cells(1, i), .Cells(.Rows.Count, i).End(xlUp)).Copy _
                    Destination:=Sheets("Boys Data").Cells(1, n)
            End With
           
            i = i + 1
            n = n + 2
        Loop

    End Sub


    Regards, OssieMac

    Friday, September 18, 2015 3:49 AM
  • OssieMac,

    Thank you very much for the help! I completely overlooked the portion where the row starts at one. Your code led me in the right direction and I got my program to perform the operation I wanted finally. 

    Best Regards

    Friday, September 18, 2015 2:21 PM