Copying specific data from workbook A (houses code) to workbook B

Question

I've been working at this for a bit. I have data in one column, separated by headers. Based off the user's interactions with the GUI, I want to be able to select portions of the data, that will then be written to a separate workbook. I have the rows for the data indexed into a variable. I think I have basically got it, but my indexing is not working for some reason when I use numerical indexing (eg Range(Cells(1, 1), Cells(6, 1))

I'm mainly confused by why writing out the index as
Cells.Range(Cells(1, 1), Cells(6, 1)).Value = ParentData.Range(Cells(6, 1), Cells(11, 1)).Value doesn't work, but

Cells.Range(Cells(1, 1), Cells(6, 1)).Value = ParentData.Range("A6:A11").Value does.

The data I'm copying is just strings, so I'm not sure that copy/paste special would be a better route. I've search around some other stackoverflow.

Private Sub Complete_Click()
Set parentWorkbook = Workbooks.Open(Application.ActiveWorkbook.FullName)
If OptionButton1 Then
    ActiveSheetStr = "Sheet1"
End If
If OptionButton2 Then
    ActiveSheetStr = "Sheet2"
End If
Worksheets(ActiveSheetStr).Activate
Set ParentData = Sheets(ActiveSheetStr).UsedRange.Select

With parentWorkbook.Sheets(ActiveSheetStr)
    Set FoundCell = .Cells.Find(What:="Header 1")
    If Not FoundCell Is Nothing Then
        datRowStart = FoundCell.Row
        datColStart = FoundCell.Column
    End If
    Set FoundCell = .Cells.Find(What:="Header 2")
    If Not FoundCell Is Nothing Then
        datRowEnd = FoundCell.Row
        datColEnd = FoundCell.Column
    End If
End With


newBook = Workbooks.Add
With newBook
.SaveAs Filename:="newBook.xls"
End With

If CheckBox1 Then
'here's what I would actually call
'Cells.Range(Cells(1, 1), Cells(1 + (datRowEnd - datRowStart - 2), 1)).Value = parentWorkbook.Sheets(ActiveSheetStr).Range(Cells(datRowStart, datColStart), Cells(datRowEnd - 2, datColStart)).Value
'here's what I'm actually running/testing
Cells.Range(Cells(1, 1), Cells(6, 1)).Value = parentWorkbook.Sheets(ActiveSheetStr).Range(Cells(6, 1), Cells(11, 1)).Value 'does not work
Cells.Range(Cells(1, 1), Cells(6, 1)).Value = ParentData.Range("A6:A11").Value 'works
End If

End Sub

Data looks like:

Header 1

blah

Header 2

blah

Works as expected when I use the alphanumeric indexing, gives me a runtime error 1004 "Application defined or object defined error" when I use numerical indexing.

Thank you for reading and for the help.

score 0 · Accepted Answer · answered Jul 31 '19 at 03:11

Cells.Range(Cells(1, 1), Cells(6, 1)).Value = _
                   ParentData.Range(Cells(6, 1), Cells(11, 1)).Value

This will fail when ParentData is not the active sheet, because your calls to Cells() will default to the activesheet, even though they're wrapped in a Range() scoped to ParentData.

This should work:

Cells.Range(Cells(1, 1), Cells(6, 1)).Value = _
           ParentData.Range(ParentData.Cells(6, 1), ParentData.Cells(11, 1)).Value

See: What is the default scope of worksheets and cells and range?

Copying specific data from workbook A (houses code) to workbook B

1 Answers1