Macro & Lookup to Paste Values

[Symmo]

Hi,

I have several Macros on this theme I would like to create all with the aim of copying a column and pasting values elsewhere in the worksheet according to what the current month is. Say I have a cell B2 with a dropdown of Months (Aug-Jul). I have a range "Spend_In_Scope_Forecast" which is my column to be copied, and another range "SIS" which is 12 columns wide, one for each month Aug-Jul. These are to capture monthly variances of these figures, and are used for charts etc.

I have put in a button to click when I want to perform the copy and have managed to get the range to copy to the August column by naming a 1 column range called "SIS_Aug", using the following code but I'm afraid this is as far as my Macro competence takes me!

Sub SISCopy()
Range("Spend_In_Scope_Forecast").Select
Selection.Copy
Range("SIS_Aug").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I would be very grateful if anyone can help out. I have an example of the document if that helps.

Thanks in advance!

 

[Jeffrey Smith]

I think I'm reading your question right, if not, let me know. I created a pulldown with months loaded from a range of cells. I created a named range for the control cell that stores the result of the pulldown. I also created a named range that is the header for the table you want to paste the values. The named range is Called SISheader. IT would be one cell above the august range.

Jeff

Sub SISCopy()
  
  Dim R As Range, M As Range
  
  Set R = Range("SelectedMonth")  'This is the control cell for the pulldown
  Select Case R.Value
    Case 1      'First month is august
      Set M = Range("SISHeader").Offset(1, 0)
    Case 2      'Second month is September
      Set M = Range("SISHeader").Offset(1, 1)
    Case 3
      Set M = Range("SISHeader").Offset(1, 2)
    Case 4
      Set M = Range("SISHeader").Offset(1, 3)
    Case 5
      Set M = Range("SISHeader").Offset(1, 4)
    Case 6
      Set M = Range("SISHeader").Offset(1, 5)
    Case 7
      Set M = Range("SISHeader").Offset(1, 6)
    Case 8
      Set M = Range("SISHeader").Offset(1, 7)
    Case 9
      Set M = Range("SISHeader").Offset(1, 8)
    Case 10
      Set M = Range("SISHeader").Offset(1, 9)
    Case 11
      Set M = Range("SISHeader").Offset(1, 10)
    Case 12
      Set M = Range("SISHeader").Offset(1, 11)
  End Select
  Range("Spend_In_Scope_Forecast").Copy
  M.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
End Sub

 

[Symmo]

Hi Jeff,

Thanks very much for helping out so quickly! I have tried it and set all the ranges as mentioned, making only one tweak - changing the Offset functions to "Offset(2, x)" as there is a secondary header row beneath the "SISHeader" range.

However, when I run the Macro by clicking the button I get the following error:

"Run time error '91':
Object variable or With block variable not set"

When I clicked 'Debug', the following code was highlighted as the error:

M.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Any ideas?

Thanks again

 

[Jeffrey Smith]

I ran it once on my end so I know it works. Here's some suggestions. The SISHeader needs to be a named range with a workbook scope. Look in the name manager. If that is the case or doesn't fix it then we'll need to reference the to include the sheet name.

Set M = Sheets("OtherSheet").Range("SISHeader").Offset(1, 0)

I don't suppose you want to email me the workbook?

 

[Jeffrey Smith]

I hope those changes I made helped.

 

[Symmo]

Hi Jeff,

The changes worked a dream, and I successfully replicated for all other fields that needed to be copied monthly.

Thanks so much for your help, much appreciated!

 

[Jeffrey Smith]

Cool!