Automated copying of worksheet to another worksheet.

0

HI,

I have a problem with my codes, everytime I run it, it shows an error "Object Required".. I already changed worksheet names and in the codes window..

What could be missing? Any help would be appreciated.

Sub copycolumns()

Dim lastrow As Long, erow As Long

lastrow = SL1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

If SL1.Cells(i, 14) = "OK" Then

SL1.Cells(i, 1).Copy

erow = Print1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 1)

SL1.Cells(i, 2).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 2)

SL1.Cells(i, 3).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 3)

SL1.Cells(i, 4).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 4)

SL1.Cells(i, 5).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 5)

SL1.Cells(i, 6).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 6)

SL1.Cells(i, 7).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 7)

SL1.Cells(i, 8).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 8)

SL1.Cells(i, 9).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 9)

SL1.Cells(i, 10).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 10)

SL1.Cells(i, 11).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 11)

SL1.Cells(i, 12).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 12)

SL1.Cells(i, 13).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 13)

SL1.Cells(i, 14).Copy

SL1.Paste Destination:=Worksheets("Print1").Cells(erow, 14)

End If

Next i

Application.CutCopyMode = False

Range("A1").Select

End Sub

Answer
Discuss

Answers

0
Selected Answer

In your code you refer to both SL1 and Print1 without having declared them as variables. If you add Option Explicit at the top of your code VBA will alert you to such omissions. I have commented and corrected your code below. But then I also re-wrote it in a way which appears more efficient. You will find that second version appended at the bottom.

Option Explicit
Sub CopyColumns()
    ' always declare all the variables you are going to use
    Dim WsIn As Worksheet, WsOut As Worksheet
    Dim lastRow As Long, eRow As Long
    Dim i As Long
    
    ' your problem hails from the fact that your code refers to
    ' worksheet objects which weren't declared and therefore not available
    Set WsIn = Worksheets("Sl1")
    Set WsOut = Worksheets("Print1")
    ' simple Rows.Count counts the rows in the ActiveSheet
    ' that may be the same as wsin but it isn't wsin which is referred to
    lastRow = WsIn.Cells(WsIn.Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        ' wsin.Cells(i, 14) refers to the range whose default property is Value.
        ' the Value property is implied but it will save you gray hair one day
        ' when you write it into the code so you can see what you mean.
        If WsIn.Cells(i, 14).Value = "OK" Then
            ' wsin.Cells(i, 1) refers to a range. The next command copies the
            ' range, with dozens of properties, not merely its Value
            WsIn.Cells(i, 1).Copy
            
            ' why so complicated, using Offset? The next row is at +1
            eRow = WsOut.Cells(WsOut.Rows.Count, 1).End(xlUp).Row + 1
            ' Worksheets("Print1") = WsOut
            ' You can refer to it by either name but better use only one
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 1)
            WsIn.Cells(i, 2).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 2)
            WsIn.Cells(i, 3).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 3)
            WsIn.Cells(i, 4).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 4)
            WsIn.Cells(i, 5).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 5)
            WsIn.Cells(i, 6).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 6)
            WsIn.Cells(i, 7).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 7)
            WsIn.Cells(i, 8).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 8)
            WsIn.Cells(i, 9).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 9)
            WsIn.Cells(i, 10).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 10)
            WsIn.Cells(i, 11).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 11)
            WsIn.Cells(i, 12).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 12)
            WsIn.Cells(i, 13).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 13)
            WsIn.Cells(i, 14).Copy
            WsIn.Paste Destination:=Worksheets("Print1").Cells(eRow, 14)
        End If
    Next i
    
    Application.CutCopyMode = False
    ' the next command doesn't specify a worksheet
    ' therefore the range will be selected on the ActiveSheet
    Range("A1").Select
End Sub
Sub CopyColumns_Revised()
    ' 24 Aug 2018
    Dim WsIn As Worksheet, WsOut As Worksheet
    Dim lastRow As Long, eRow As Long
    Dim R As Long
    
    Set WsIn = Worksheets("Sl1")
    Set WsOut = Worksheets("Print1")
    
    Application.ScreenUpdating = False      ' speeds up execution!
    With WsIn
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For R = 2 To lastRow
            If .Cells(R, 14).Value = "OK" Then
                eRow = WsOut.Cells(WsOut.Rows.Count, 1).End(xlUp).Row + 1
                Range(.Cells(R, 1), .Cells(R, 14)).Copy Destination:=WsOut.Cells(eRow, 1)
            End If
        Next R
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    With WsOut
        .Activate      ' can only select a range on the ActiveSheet
        .Cells(1, 1).Select
    End With
End Sub
Discuss

Discussion

Thank you very much for your time...Will work on it soon..God bless you!
rowel (rep: 2) Aug 26, '18 at 9:12 pm
Thank you so much for solving my problem in my excell codes! It worked.. (the second code that you have provided) The only thing that i cannot figure out is that, all of the data that were automatically copied to the target sheet have missing links....maybe because the copied data (with links from other sheets) is the problem? How can I automatically copy from 1 worksheet to the other without the links on the source sheet? Thanks in advance...
rowel (rep: 2) Sep 8, '18 at 9:04 pm
I fear that your question will not find a satisfactory answer without a workbook to show the nature of the missing "links". How can data "have missing links"? (If they have links they aren't missing and if they are missing they don't have them. And this is before I ask what a "link" is in this case.) You might attach such a workbook to your original question.
Meanwhile, to bring you up to speed, a cell has a lot of "properties", incl its colour, the size of its font, its frame, address and many more. When a cell is copied everything is copied. Two of the properties of particular interest are those of Value and Formula. When a cell is copied the Formula copied with it must be able to function in its new environment. That is a question of formula design (and not to be treated in this discussion, please). On the other hand it's possible to copy Values only, leaving all other properties behind. The Value of a cell is the result of the formula it may contain, in most cases the "value" that you see displayed in the original cell.
Variatus (rep: 2688) Sep 8, '18 at 11:09 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login