Copy Data between Workbooks

[DenialDan]

Hello,

I've been trying to get this to work:
I have one workbook which contains new data every week. When I run the macro I want it to open that workbook and copy data from a set range into the workbook that contains the executed macro.
The set range to copy is from one cell (top left) to the end of the worksheet 6 (bottom right). I was able to save the bottom left cell to copy into a variable but I can't figure out a way to use it in my copy range.

Private Sub ADRMoP_Click()


Dim ADRM As Workbook
Set ADRM = Workbooks.Open("<Workbook Address on Drive>")


Dim EndCell As Range
' Bottom left cell as variable
Set EndCell = ADRM.Sheets(6).Range("A1").SpecialCells(xlCellTypeLastCell).Offset(-2, 0)


Dim CopyRange As Range
' Here's my problem. I can't put the variable into my range method
Set CopyRange = ADRM.Sheets(6).Range("B3:EndCell.Address()")
CopyRange.Copy
Sheets("Line Items_oP").Range("B4").Paste


ADRM.Close


End Sub

Kind regards,
Daniel.

 

[gallen]

Hello and welcome.

If i undertsand correctly you just need to change this line:

ADRM.Sheets(6).Range ("B3:" & EndCell.Address)

 

[DenialDan]

Hello gallen and thank you for the fast reply,

That did indeed solve my original issue, but now I'm getting a 'subscript out of range' error.
Could the reason be that I've been trying to paste the copy range into a single cell (I didn't want to specify the paste range as well)

 

[gallen]

On which line do you get the error?

 

[DenialDan]

I've changed a little bit while trying to find the issue so here's a part of the code:

Dim CopyRange As Range
Set CopyRange = ADRM.Sheets(6).Range("B3:" & EndCell.Address)
CopyRange.Copy
[B]ThisWorkbook.Sheets("Line Items_oP").Range("B4").PasteSpecial xlPasteValues[/B]

The bold part is where I get the error.

 

[gallen]

Works fine for me.

Ensure that 'Line Items_oP' is spelt correctly.

Also ensure that 'ThisWorkbook' is the workbook that contains the sheet named 'Line Items_oP'

 

[DenialDan]

I've changed the Line where I paste to:

ThisWorkbook.Sheets(6).Range("B4").PasteSpecial xlPasteValues

I've checked in Visual Basic, the Worksheet I need is number 6 and ThisWorkbook is the one where the macro is located and the sheet I want to paste the values in is also in there.

Now that I changed it to Sheets(6) I get the error message: PasteSpecial method of Range class failed

 

[DenialDan]

After changing up my code I've gotten another error message: Object doesn't support this property or method.
This is the code I've used:

Private Sub ADRMoP_Click()


Dim ADRM As Workbook
Set ADRM = Workbooks.Open(" ")


Dim EndCell As Range
Set EndCell = ADRM.Sheets(6).Range("A1").SpecialCells(xlCellTypeLastCell).Offset(-2, 0)


[B]ADRM.Sheets(6).Range("B3:" & EndCell.Address).Copy_[/B]
    This.Workbook.Sheets(6).Range ("B4")


End Sub

The bold line is where I got the error message.

 

[gallen]

Do you have any merged cells in either of the ranges?

 

[DenialDan]

I've selected both full ranges and unmerged them, sadly I still get the message.