CODEWITHSUNDEEP
vbaexcel
Kyle C. Dwyer
Kyle C. Dwyer

Reputation: 13

Unable to define proper Object in VBA for a Copy Paste Template Macro

I'm trying to create a macro that copies a range of cells that has a template with text set up and pastes that template into another range of cells on a different sheet in one workbook.

Here's what I have: 

Option Explicit

Dim ClinObTemplate As Variant
ClinObTemplate = Sheets("clin obs").Range("A397, [D429]")

Dim NewTemplate As Variant
NewTemplate = Sheets("clin obs 2").Range("A430, [D462]")

Sub ClinOb_Copy_Paste()

ClinObTemplate.Select
Selection.Copy
NewTemplate.Select
Selection.Paste


End Sub

Should I define these variables As String? I keep receiving Runtime Error 424: Object Required, but I can't seem to define my object.

I'm brand new to VBA and programming, so I really appreciate the help!

Thank you!

Upvotes: 1

Views: 152

Answers (3)

Telestia
Telestia

Reputation: 300

You should define them as Excel's built in Range type. You should also be defining them inside the subroutine, unless you need them across multiple subs or functions. You should also read this: Avoiding using select.

After cleaning up the code a bit, this is what I came up with.

Sub ClinOb_Copy_Paste()
 Dim ClinObTemplate As Range
 ClinObTemplate = Sheets("clin obs").Range("A397, [D429]")

 Dim NewTemplate As Range
 Set NewTemplate = Sheets("clin obs 2").Range("A430, [D462]")
 ClinObTemplate.Copy NewTemplate
End Sub

I hope this helps!

Upvotes: 2

L42
L42

Reputation: 19727

There are several reasons your code fails.

If you want to work with objects, you assign them to variables using Set. Even if you declare your variable as Variant type, it will still work provided you use Set. However, it is always advisable to explicitly declare the type of variable you want to work with. Why?

  • It helps in stability, readability and allows you to detect type mismatches.
  • It is faster.

So this would have worked:

Dim ClinObTemplate As Variant
Set ClinObTemplate = Sheets("clin obs").Range("A397,D429")

But of course, what Dan posted is advisable.
Now, as you can see there's a difference in how I've constructed the Range Object syntax.
Let's take a look at your code:

Dim ClinObTemplate As Variant
ClinObTemplate = Sheets("clin obs").Range("A397, [D429]")

Above would've resulted to Error 1004.
If you've noticed, I remove the [] brackets on the D429 address.
Why? Because [] have special meaning in VBA.
It is a shortcut for Evaluate.
[D429] is actually evaluating the address D429 and actually returns a Range Object. So to make your code work with brackets [], it should be like this:

Dim ClinObTemplate As Variant
Set ClinObTemplate = Sheets("clin obs").Range("A397," & [D429].Address)

Now your ClinOnTemplate variable contains the Range Object A397 and D429.
If however you want to select the entire range in between those boundaries like A397:D429, write your code like this:

Dim ClinObTemplate As Variant
Set ClinObTemplate = Sheets("clin obs").Range("A397", [D429])

Or this:

Dim ClinObTemplate As Variant
Set ClinObTemplate = Sheets("clin obs").Range("A397:" & [D429].Address)

Or even this:

Dim ClinObTemplate As Variant
Set ClinObTemplate = Sheets("clin obs").Range([A397], [D429])

All codes assigns the Range Object A397:D429 to ClinObTemplate variable.

Upvotes: 1

Dan Wagner
Dan Wagner

Reputation: 2713

Try this out:

Option Explicit
Sub ClinOb_Copy_Paste2()

Dim ClinObRange As Range, NewObRange As Range
Dim ClinObsSheet1 As Worksheet, ClinObsSheet2 As Worksheet

'set references up-front
Set ClinObsSheet1 = ThisWorkbook.Worksheets("clin obs")
Set ClinObsSheet2 = ThisWorkbook.Worksheets("clin obs 2")
With ClinObsSheet1
    Set ClinObRange = .Range("A397:D429")
End With
With ClinObsSheet2
    Set NewObRange = .Range("A430:D462")
End With

'do work
ClinObRange.Copy NewObRange

End Sub

Upvotes: 1

Related Questions