VBA: Copy specific cells from one workbook to another

[ParaSitius]

Hi all,

I'm looking for help in changing the following code as it is currently set to copy from a specific cell, the problem I have is that I have no guarantee that the data will stay in those same cells. The one constant is that the figures will always be at the bottom of the column even if rows are added or removed above them.

Sub Code()
Dim x As Workbook
Dim y As Workbook


'## Open Data File (x) and Final Report (y) workbooks first:
Set y = Workbooks.Open("file location<location of="" final="" report="">")
Set x = Workbooks.Open("file location<location of="" data="" file="">")


'Now, transfer values from x to y:
y.Sheets("Cover Summary").Range("C8").Value = x.Sheets("Worksheet").Range("D400")
y.Sheets("Cover Summary").Range("D8").Value = x.Sheets("Worksheet").Range("D401")
y.Sheets("Cover Summary").Range("F8").Value = x.Sheets("Worksheet").Range("D403")
y.Sheets("Cover Summary").Range("H8").Value = x.Sheets("Worksheet").Range("D404")

The totals in the Data File range from D400 to D405 but I only require 4 of the 6 figures for the report, I will also add that the the way the data file is formatted, the data starts from Cell D10 and there are some cells containing no data in the column.

I've tried searching the internet but all the coding that I've found keep throwing back errors of one kind or another.

Thanks in advance.</location></location>

 

[Yongle]

Use Named Ranges because they autoReference correctly when rows are inserted or deleted above

Name each of the 4 cells using the Name Box above cell A1
- select each cell, type its name into the name box (NO SPACES OR FUNNY CHARACTERS ALLOWED!) and hit {ENTER}
- cannot use cell references etc

The workbook is activated so that VBA looks in the correct place for the 4 named ranges

x.Activate
y.Sheets("Cover Summary").Range("C8").Value = [Name_1]
y.Sheets("Cover Summary").Range("D8").Value = [Name_2]
y.Sheets("Cover Summary").Range("F8").Value = [Name_3]
y.Sheets("Cover Summary").Range("H8").Value = [Name_4]

[ around the name ] evaluates the value

 

[Fluff]

Another option

With x.Sheets("Worksheet").Range("D" & Rows.Count).End(xlUp)
   y.Sheets("Cover Summary").Range("C8").Value = .Offset(-5).Value
   y.Sheets("Cover Summary").Range("D8").Value = .Offset(-4).Value
   y.Sheets("Cover Summary").Range("F8").Value = .Offset(-2).Value
   y.Sheets("Cover Summary").Range("H8").Value = .Offset(-1).Value
End With

 

[ParaSitius]

Thank you Yongle, I can't use your suggestion in this case but I can for another data file.

Thank you as well Fluff, your code is exactly what I needed for this particular situation.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Yongle]

Yongle, I can't use your suggestion in this case

I am intrigued .. what is preventing my suggestion from working in this case ?

 

[ParaSitius]

Hi Yongle,

It may well be my lack of understanding as VBA is still relatively new to me but the data file I get is newly created each time, and the way I read your code it would mean I'd have to manually change the cell type each time.

What I failed to mention in my OP is that I actually receive 24 of these files and Fluff's code fitted the requirements I needed.

 

[Yongle]

You are correct
- to use my code would involve creating the named ranges for every workbook for "once off" usage
- they could be auto-created by using the method employed in Fluff's code to identify the 4 cells
- but that would be pointess when Fluff's code goes stright to the cells