Excel 2016 copy range of cells - range determined by cell results on another sheet

Question

I have looked for an answer to this but cannot find one that fits.

I have a two sheet excel spreadsheet which I wish to add a VBA macro to. The second sheet has column 1 with a number of skills, columns 2 to 13 have the "scores" for those skills with each column representing a month. Sheet 1 has a form where the following are selected from drop down lists.. skill being scored, month that the person was last assessed and month that the person is being assessed. I have managed to display the relevant "last month" score for the skill being assessed but now wish to copy the scores for all the skills from that month's column to the column representing the month that the person is being assessed. I will then overwrite just the specific skill score with the new data. This is what I've tried so far...

With Worksheets("Sheet2")
  Range(Cells(2,Sheet1!D17),Cells(Sheet1!D19,Sheet1!D17).Copy Range(Cells(2,Sheet1!D20),Cells(Sheet1!D19,Sheet1!D20)
End With

Where D17 contains the column number of the column to be copied, D19 contains the calculated last row of the column, D20 contains the column number of the column where the data needs to be pasted. I've tried using cells on Sheet2 to contain these values in case it was a problem referencing Sheet1 but get the same problem. If I replace variables by numbers it works.

This looks to be a problem with using variables within the Cells() construct. Any pointers please?

I've since tried the following:

With Worksheets("Sheet1")
  oldMonthCol = Range("D17").Value
  newMonthCol = Range("D20").Value
  lastRow = Range("D19").Value
End With

These variables were defined as Long prior to this code block. They were then used in the expression previously given to replace Sheet1!D17 etc. The code compiles but when I look at the values of oldMonthCol etc it is 0 when it should be a non-zero number. Any thoughts please?

Just found out that it is modifying Sheet1 despite having Worksheets set to Sheet2...

Solved this bit by using the following:

With Worksheets("Sheet2").Select
  Range(Cells(2,oldMonthCol),Cells(lastRow,oldMonthCol).Copy Range(Cells(2,newMonthCol),Cells(lastRow,newMonthCol)
End With

This only works with values that I have entered into the cells rather than the result of a formula.

Solved! Sub Button2_Click()

Dim skillName As String
Dim newScore As Long
Dim skillNameRow As Long
Dim oldMonth As String
Dim newMonth As String
Dim oldMonthCol As Integer
Dim newMonthCol As Integer
Dim lastRow As Integer
Dim oldMonthCol2 As Integer
Dim newMonthCol2 As Integer
Dim lastRow2 As Integer
Dim skillNameRow2 As Integer
Dim newSkillValue As Integer


With Worksheets("Sheet1")
  skillName = Range("D3").Value
  skillNameRow = Range("D16").Value
  skillNameRow2 = skillNameRow + 1
  oldMonth = Range("G3").Value
  newMonth = Range("G5").Value
  oldMonthCol = Range("F17").Value
  oldMonthCol2 = oldMonthCol + 1
  newMonthCol = Range("F20").Value
  newMonthCol2 = newMonthCol + 1
  lastRow = Range("F19").Value
  lastRow2 = lastRow + 1
  newSkillValue = Range("F21").Value
End With

With Worksheets("Sheet2").Select

  Range(Cells(2, oldMonthCol2), Cells(lastRow, oldMonthCol2)).Copy     Range(Cells(2, newMonthCol2), Cells(lastRow, newMonthCol2))
  Worksheets("Sheet2").Activate
  Cells(skillNameRow2, newMonthCol2).Select
  ActiveCell.Value = newSkillValue

End With

End Sub

Not elegant but it works. There are some variables that are not yet used but they are intended for a future add on...

Welcome to SO. What did you try so far, and what is causing problem? I encourage you to read the following posts: [How do I ask a good question](http://stackoverflow.com/help/how-to-ask). — Vincent G, Aug 18 '16 at 13:13
Please edit your post instead of posting long code in comments. — Vincent G, Aug 18 '16 at 15:14
Sorry for breaching etiquette. I've now added the text and code segment within the question. Thanks for your patience with me. Regards. — Nev, Aug 18 '16 at 15:32
Inside your `With` block, you need to qualify the ranges like `.Range("D17").Value` otherwise it's assuming the range on the *ActiveSheet* which probably isn't Sheet1. — David Zemens, Aug 18 '16 at 18:14

David Zemens · Accepted Answer · 2016-08-18T18:30:09.663

0

The With statement requires that you qualify ranges/child objects, otherwise they are not "with" the parent, and in the case of Range or Cells, etc., will default to the ActiveSheet:

You can see this in action:

Worksheets("Sheet2").Activate
With Worksheets("Sheet1")
    Debug.Print Range("A1").Parent.Name   '## This will print "Sheet2"
    Debug.Print .Range("A1").Parent.Name  '## This will print "Sheet1"
End With

in your code, then:

With Worksheets("Sheet1")
  oldMonthCol = .Range("D17").Value
  newMonthCol = .Range("D20").Value
  lastRow = .Range("D19").Value
End With

And probably:

With Worksheets("Sheet2")
  .Range(.Cells(2,oldMonthCol),.Cells(lastRow,oldMonthCol).Copy .Range(Cells(2,newMonthCol),.Cells(lastRow,newMonthCol)
End With

edited Aug 18 '16 at 18:30

answered Aug 18 '16 at 18:18

David Zemens

53,033
11
81
130

Thanks. I've just seen your reply after I'd posted my, now working, code. Not as elegant as yours but it works for the moment. I'll modify it with your solution when I get a spare minute. Thanks again. – Nev Aug 18 '16 at 19:12
Cheers, I would modify it that way you don't need to keep track of which sheet is Active ([which can lead to tons of problems & frustration when trying to debug...](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)) and also because your `With` blocks are not having any effect unless you use the `.` qualification for the ranges :) If this helps, do consider [accepting](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) the answer. – David Zemens Aug 18 '16 at 19:17

Excel 2016 copy range of cells - range determined by cell results on another sheet

1 Answers1