CODEWITHSUNDEEP
vbaexcel
ian
ian

Reputation: 19

Copy selected cells from one workbook and copy to another

I am trying to create a database which will copy a selected range of data from a main workbook and copy into a separate workbook.

The code causing the issue is below. The 2nd workbook opens based on the value of "W2". A new row should be inserted to the new Wb and formatted then the value of the selected cells pasted.

'Select data to be copied
ActiveCell.Resize(1, 4).Copy

'Open Lessons Learned Db
Location = Range("W2").Value
Set Lessons = Workbooks.Open(Location)
Set LL = Sheets("Lessons Learned")
Windows("Lessons Learned Database.XLSM").Activate
Sheets("Lessons Learned").Activate

'Insert New Row
Range("5:5").Activate
ActiveCell.Offset(1).EntireRow.Insert
'Enter Odd Or Even VALUE
    Range("A7").Select
    OE = ActiveCell.Value
    If OE = 1 Then
        Range("A6").Select
        ActiveCell.FormulaR1C1 = 0
    Else
        Range("A6").Select
        ActiveCell.FormulaR1C1 = 1
    End If

'Hide Permanently Hidden Rows -LINE BELOW GIVES ERROR 1004
    Rows("5:5").Select
    Selection.EntireRow.Hidden = True
    Columns("A").Select
    Selection.EntireColumn.Hidden = True
'FORMAT ROW
    Range("A6").Select
    SC = ActiveCell.Value
        If SC = 1 Then
    Range("B6:N6").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With

End If
Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Any pointer on where I'm going wrong would be greatly appreciated.

Upvotes: 1

Views: 636

Answers (3)

SierraOscar
SierraOscar

Reputation: 17637

That can all be consolidated to:

ActiveCell.Resize(1, 4).Copy '// not sure what this is for

Set Lessons = Workbooks.Open([w2])
Set LL = Lessons.Sheets("Lessons Learned")

With LL
    .Rows(6).EntireRow.Insert
    .Range("A6").value = IIf(.Range("A7").value = 1, 0, 1)
    .Rows(5).Hidden = True
    .Columns("A").Hidden = True
        If .Range("A6").value = 1 Then
            With .Range("B6:N6").Interior
                .ColorIndex = 15
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        End If
    .Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
End With

You will notice that this code refers directly to each object without activating or selecting it. Coding in this way means that every object is fully qualified and you know exactly which instance you are working with.

This should ensure that the row is correctly hidden without error because you are referring to the Rows collection which is a collection of Ranges with defined properties and methods. Selection can be a sheet, workbook, chart, range or pretty much anything else you can point and click at - so this can cause problems when trying to access properties or methods that belong to a particular object or class.

Upvotes: 1

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

I suspect your code has some other issues that Code Review may be able to help with but to answer your question:

The Rows("5:5").Select is being passed the wrong argument data type.

Worksheet.Rows() is expecting a number, either Integer or Long data type but you are giving it a string.

Change it to Rows(5) and it should work.

Upvotes: 1

DragonSamu
DragonSamu

Reputation: 1163

Its advised not to use .Select or .Activate as there are other ways to accomplish this.
Because you were selecting and activating this could have caused the ERROR 1004.

Below i have "cleaned up" your code defining Lessons, LL and Location and included MainWB and defined your ranges.

By defining your Range Excel will always get the .Value from that Range there is then no need to use .Select or .Activate.

As far as tested the below code works:

Sub CopyMainWBtoNewWB()

Dim Lessons As Workbook
Dim LL As Worksheet
Dim MainWB As Workbook
Dim Location As String

Set MainWB = Workbooks("Name Here")

'Open Lessons Learned Db
Location = MainWB.Sheets("Sheet Name").Range("W2").Value
Set Lessons = Workbooks.Open(Location)
Set LL = Lessons.Sheets("Lessons Learned")

'Insert New Row
LL.Rows(5).Offset(1).EntireRow.Insert shift:=xlDown

'Enter Odd Or Even VALUE
If LL.Range("A7").Value = 1 Then
    LL.Range("A6").Value = 0
Else
    LL.Range("A6").Value = 1
End If

'Hide Permanently Hidden Rows -LINE BELOW GIVES ERROR 1004
LL.Rows(5).Hidden = True
LL.Columns(1).Hidden = True

'FORMAT ROW

If LL.Range("A6").Value = 1 Then
    With LL.Range("B6:N6").Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
End If
MainWB.Sheets("Sheet1").Range("A1:A4").Copy
LL.Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

All you need to do is change the Workbook name of MainWB and the Sheet name its collecting the Valuefor Location.

Upvotes: 1

Related Questions