CODEWITHSUNDEEP
vba
austinmb
austinmb

Reputation: 69

Why do I get a compile error in this macro

I am trying to write a copy/paste as values macro that involves a drop down menu. I think I have the "drop down" part, but I need Excel to copy/paste as values one row at a time before plugging in the next drop down value.

What I need help figuring out is do I have the 1)"d8 = d" in the right order, should it be moved up 2) is the syntax for the copy/paste range correct?

3) how do I tell it to Copy/paste as values one row at a time?

Here is the sheet with the cells I want to copy/paste as values enter image description here

Will this code give me what I want in terms of plugging in the dropdown, letting the formulas calc, and the copying and pasting one line at a time?

Sub Copy_Paste()
Sheets("MS Wall Summary Weekly View").Activate
    Dim vRws As Long, vRng As Range
    Dim d As Range, d8 As Range, Wst As Worksheet
    Dim vRng2 As Range, vRws2 As Long, i As Long

Set Wst = Worksheets("MS Wall Summary Weekly View")
Set d8 = Wst.Range("D8")

With Wst
   vRws = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set vRng = Range(.Cells(2, "A"), .Cells(vRws, "A"))
End With

For Each d In vRng.Cells

d8 = d
Sheets("Email").Activate
With ActiveSheet
    vRws2 = .Cells(.Rows.Count, "U").End(x1Up).Row
    For i = 8 To vRws2
        Set vRng2 = Range("U" & i & "AC" & i)
            vRng2.Copy
                vRng2.PasteSpecial Paste:=xlPasteValues
End With

Next d
Next i

Upvotes: 0

Views: 33

Answers (1)

urdearboy
urdearboy

Reputation: 14580

The nice thing about compile errors is that they tell you exactly where the error exists. You had typos and a invalid string build on the marked lines below

  1. Error 1: ...End(x1Up) (you swapped the number 1 with letter l).
  2. Error 2: Set vRng2 = Range (""U" & i:"AC"& i"). You have double quotes around the U and you did not enclose the : inside quotes. You also left i inside the quotes. Please see correct method in code below
Option Explicit

Sub Copy_Paste()
Sheets("MS Wall Summary Weekly View").Activate
    Dim vRws As Long, vRng As Range
    Dim d As Range, d8 As Range, Wst As Worksheet
    Dim vRng2 As Range, vRws2 As Long, i As Long

Set Wst = Worksheets("MS Wall Summary Weekly View")
Set d8 = Wst.Range("D8")

With Wst
   vRws = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set vRng = .Range(.Cells(2, "A"), .Cells(vRws, "A"))
End With

For Each d In vRng.Cells

d8 = d
Sheets("Email").Activate
With ActiveSheet
    vRws2 = .Cells(.Rows.Count, "U").End(xlUp).Row '< -- Here
    For i = 8 To vRws2
        Set vRng2 = .Range("U" & i & "AC" & i)      '< -- Here
    Next d

A couple of other issues at a glance

  1. Declare your MS Wall Summary Weekly View sheet variable right away and remove the .Activate line at the start
  2. You are not using your With block all the way. Your Range properties need to be prefixed with a dot
  3. You declared d8 as a range but you set it equal to d. You may need to revisit what the goal is here as this is not a valid range
  4. Building a multiple-cells range may be easier using the Cells property (On Error 2: above). You can use .Range(.Cells(i, "U"), .Cells(i, "AC")) instead (note the qualifier dot, going against the With ActiveSheet block variable)

Upvotes: 2

Related Questions