0

I am making some VBA code to help me do the following:

Paste a list of all named ranges Loop through the list and copy/select ranges based on the list Each selection will be pasted on another sheet given an address reference with a certain offset from that address

I am pretty new to VBA so I have put together some code that I think will do the trick but I am getting run time errors. Could someone help me troubleshoot or provide suggestions?

My code is here:

Sub RangeLoop()

Sheets("RANGEMATCH").Select
Range("A1").ListNames

Dim columnrange As Range
Dim m As Long
Dim address As Range

Set columnrange = Sheets("RANGEMATCH").Range("A:A").SpecialCells(xlConstants)

With columnrange
    For m = 1 To columnrange.Areas.Count
        Set address = Sheets("RANGEMATCH").Range(.Areas(m).Cells(1, 7).Value)
        Range(m).Copy Sheets("ETIE").Range(address.Offset(1, 10))
    Next
End With

End Sub

Here is an example workbook of what I am working with:

https://docs.google.com/spreadsheet/ccc?key=0AodOP_8DnJnFdHJoQ0xBM3JUUGJxT3EyRXN0T2ltUmc&usp=sharing

Any suggestion are appreciated.

Community
  • 1
  • 1
eMTy
  • 328
  • 1
  • 8
  • 20
  • 2
    You may benefit from these two tips: use `Option Explicit` — [see TLiebe's answer](http://stackoverflow.com/questions/2454552/whats-an-option-strict-and-explicit) and [avoid using `Select`](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — including avoiding using `Activate` – JustinJDavies Feb 05 '13 at 08:07
  • Also, you may want to tell us what the error is, and which line is it on as well. – Larry Feb 05 '13 at 09:24
  • The error shows up on this line: Range(m).Copy Sheets("ETIE").Range(address.Offset(1, 10)) – eMTy Feb 05 '13 at 14:20

1 Answers1

0

The Range() function expects a string variable. Either the name of a range or a cell reference like "A1"

Range(m).Copy Sheets("ETIE").Range(address.Offset(1, 10))

m is a number so that won't work. You'll need to reference the thing that you want to copy. I think that might just be your address object that you set in the line before:

address.Copy Sheets("ETIE").Range(address.Offset(1, 10))
Jacob Young
  • 374
  • 3
  • 13
  • Thank you for your response. The suggestion is good but it doesnt help me here. The address variable is the destination where I want to paste my range ...the address is set to a column with multiple cell coordinates in them. Range(m) is how I am trying to identify/pull in the named range I created. I basically have a 2 lists, one with named ranges and a few cells to the right is a list of destination addresses where those ranges should be pasted. I want this loop to walk down list one (with ranges) and pull them in, at the same time walk down the second list (of addresses) and use them – eMTy Feb 05 '13 at 18:33
  • Does anyone have any other suggestions on how I can pull in the required information into the code? All I need is how to reference a cell value as a name for a named range and then use another cell value as an address on another sheet where to paste that range. Would be a huge help. Thank you! – eMTy Feb 05 '13 at 21:10
  • Are the ranges more than one cell in size? If not, you can use `Sheets("Destination").Range(Sheets("Original").Range("theAddress")) = Sheets("Original").Range("theValue")` – Jacob Young Feb 07 '13 at 15:31
  • Thank you for the suggestion. The ranges are much larger than one cell..I posted an example spreadsheet in the link at the top of the post so you can take a look at what I am dealing with. – eMTy Feb 07 '13 at 18:59