Need better solution, inelegant hack works - improve my skills

[Malekish]

Making a simple macro to open a series of workbooks, select certain cells out of them and copy to ThisWorkbook

I have it all set up and working, my code works, but I know there has to be a more elegant solution.

The cells I'm copying from are, of course, not in any logical order. Some are contiguous but most are scattered around by the person that created the template.

Essentially what I need to do is:

    wsSource.Range("B26").copy
    wsTarget.Cells(intRow, 2).pastespecial (xlPasteValues)
    
    wsSource.Range("D22").copy
    wsSource.Cells(intRow, 3).pastespecial (xlPasteValues)

etc....

Now if the Source locations were in some logical format I could do it by Range(), maybe using offset. Heck, I could even reference by Cells(intRow, intCol)

Is there a better way? I started thinking about

dim rSource1 as Range
dim rSource2 as Range
etc...


With wsSource
rSource1 = .Range("B26")
rsource2 = .Range("D22")
etc...
End With

This properly creates a bunch of ranges with the locations in them, however I can't figure out how to reference the ranges.

Dim rSourceCell as Range

For intLoopCount = 1 to XX
rSourceCell = rSource + cstr(intLoopCount)
rSourceCell.Copy

Unfortunately it gives me a type mismatch because I'm trying to pass a string to a Range variable.


So what's the best way of solving this? Thank you very much for your insight!

 

[SkipVought]

hi,

Good exercise to review and try to improve your code.

Sometimes its the process that needs to be reevaluated.

I don't often do a lot of copy 'n' paste. I more often have a TABLE as the source for my data, so the mapping is much more logical.

You might think about constructing a From-To map. Then just loop thru the table to get the sheet and cell references.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Anyway, you may replace this:
wsSource.Range("B26").copy
wsTarget.Cells(intRow, 2).pastespecial (xlPasteValues)
with this:
wsTarget.Cells(intRow, 2) = wsSource.Range("B26").Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

You may also consider, instead of:

dim rSource1 as Range
dim rSource2 as Range
etc...

With wsSource
rSource1 = .Range("B26")
rsource2 = .Range("D22")
etc...
End With

something like

Dim rSource(9) as Range
Dim i As Integer

For i = LBound(rSource) To UBound(rSource)
    rSource(i) = wsSource.Range("B26")
Next i[green]
'or[/green]
rSource(0) = wsSource.Range("B26")
rSource(1) = wsSource.Range("D22")
...

Just a guess...

Have fun.

---- Andy

 

[PHV]

For a Range object you have to use the Set instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Malekish]

Andy: Good idea, I'll implement that

PHV: Thanks for the suggestion of wsTarget.Cells() = wsSource.Range().Value



Thanks to everyone for the suggestions, I keep lurking here to improve my skills, everyone has always been the most helpful!

 

[Malekish]

Solved it! Much cleaner than what I had. I'm omitting a few lines, but here's the key part so you can see how I solved it

Dim rSource as Variant

rSource = Array(1, 2, ...)


With wsSource
rSource(1) = .Range("B26".Value
rSource(2) = .Range("D22").Value
etc...
End With



For intCount = lbound(rSource) to ubound(rSource)
rTarget.Cells(intRowCount, intColCount).Value = rSource(intCount)
Next intCount

So I'm reading the values into the array, then on the target just running thru my cells & assigning the values

Many thanks for the inspiration!

 

[Andrzejek]

I love when people have this approach to their work: it works fine now but I want to make it better.

Keep up the good work, Malekish


Have fun.

---- Andy