0

Any help would be greatly received.

I'm trying to write vba code that copies a range of cells from one workbook to another. The destination file is active and contains a date in column A; this is used to open the source file (file name contains a date), cells are then copied and paste into the destination file from the source file. The code works perfectly if i specify the destination range manually as follows:

x.Sheets("Sheet1").Range("B4:D4").Copy Destination:=y.Sheets("Sheet1").**Range("B5")**

However; i would like the destination range to be dynamic.... i would like it to be the same as the active cell. I have amended this in various ways but still cannot find a solution:

Sub OpenWb()

Dim FileDate As Date
Dim x As Workbook
Dim y As Workbook

Application.ScreenUpdating = False

FileDate = ActiveCell.Offset(0, -1)


'Define x and open y

Set y = ActiveWorkbook

Set x = Workbooks.Open("C:\Users\yatesa\Documents\" & Format(FileDate, "dd-mm-yy") & " Production Data.xlsx")

'Transfers values from x to y

x.Sheets("Sheet1").Range("B4:D4").Copy Destination:=y.Sheets("Sheet1").**Range(ActiveCell)**

'Close x

x.Close

End Sub

Many Thanks.

Community
  • 1
  • 1
Ady
  • 1
  • 1

1 Answers1

0

Thank you so much for your input; I have now resolved the problem. The active cell focus was lost when opening the source file.... probably obvious; but I just couldn't see it last night. I have added a line to store the destination active cell and was then able to recall it. I will repost the code later today for those that might be interested. Thanks once again!

Ady
  • 1
  • 1