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.

 

[DenialDan]

I've resolved the issue. The code I used at the end was the following:

Private Sub ADRMoP_Click()

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


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


Dim CopyRange As Range
Set CopyRange = ADRM.Sheets(6).Range("B3:" & EndCell.Address)
CopyRange.Copy
FC.Sheets(5).Range("B4").PasteSpecial xlPasteAll

ADRM.Close

End Sub

Weirdly enough the problem was that I wanted to paste it in Sheet 6 but it needed to be pasted into Sheet 5. In VBE it said the needed Sheet was 6 but whatever.

Thanks for the help.

 

[gallen]

All's well that ends well.