1

Who wants easy points? I am trying to set a range of columns from one sheet to another using variables. startm will always be an integer. I know I can do something like this or this but I hate offsetXD Is there a "smarter" way besides concatenating a million "s and "&"s??

For Each s In thisWB.Worksheets
...
With ws
.Columns(3:14-startm+1)=s.columns(startm:14) 'this don't work ;_;
Community
  • 1
  • 1
findwindow
  • 3,133
  • 1
  • 13
  • 30
  • What do you mean by "Set a range of columns from one sheet to another"? Like you want all of the values in one range to be copied to another range, where those two ranges are a group of columns? – JNevill Oct 15 '15 at 16:42
  • Yea, exactly but I don't want formatting so not copy/pasting. Edit: I want to transfer columns 5 to 10 from one sheet to columns 2 to 7 in another sheet. Except those numbers will vary. – findwindow Oct 15 '15 at 16:43
  • use `s.Columns(5).Resize(, 5).value = .Columns(2).Resize(, 5).value` – Scott Craner Oct 15 '15 at 16:48
  • I am going the other way and it doesn't always start at 2 or 5 XD Edit: but I like your idea. I can do something like `.columns(startm+1).resize(startm)` etc – findwindow Oct 15 '15 at 16:49
  • Fair enough what about `.Columns(3).Resize(, 14 - startm + 1).value = s.Columns(startm).Resize(, 14 - startm + 1).value` – Scott Craner Oct 15 '15 at 16:55
  • Why not post as answer and get easy points =P Resize is kinda similar to offset though but I guess it's most "efficient" and "obvious" to see what's going on at a quick glance. – findwindow Oct 15 '15 at 16:56

3 Answers3

1

Since you are working with column numbers instead of letters, the best approach here is a for loop:

For Each s In thisWB.Worksheets
...
With ws

    For col = 3 to 14-startm+1
        'may have to adjust these values in here to fit your dynamic need, but
        '  the overall idea here is sound
        .Columns(col).Value = s.Columns(col+startm).Value
    Next col
End ws
Next s

I didn't reverse engineer your startm +/- stuff here on backhalf of that .columns()... line, but this should do what you need after filling in that blank.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Sigh. Overkill isn't it? I guess I am looking for a "smart" way. – findwindow Oct 15 '15 at 16:50
  • 1
    Welcome to VBA and Excel. Just stick `On Error Resume Next` at the start of your subroutine and write whatever you want. – JNevill Oct 15 '15 at 16:55
  • You'd think msft would build it so one can write `columns(x:y)` and have it work but nooooooooooooooooo XD – findwindow Oct 15 '15 at 16:57
  • 1
    YEa.. I don't get it either. Referring to columns and ranges as letters in your programming environment. Not the most genius thing MS has ever done. – JNevill Oct 15 '15 at 16:59
1

You could try something like this:

Range(.Cells(1, 3), .Cells(1, 14 - startm + 1)).EntireColumn = _
   Range(Sheets(1).Cells(1, startm), Sheets(1).Cells(1, 14)).EntireColumn.Value

But without certain limitations, it could be slow

Demetri
  • 869
  • 1
  • 6
  • 12
  • Yea that looks so hedious -_- I guess I want something that's immediately obvious that I am going from column x to column y XD – findwindow Oct 15 '15 at 16:54
1

As requested, I would use:

.Columns(3).Resize(, 14 - startm + 1).value = s.Columns(startm).Resize(, 14 - startm + 1).value
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 2
    To make this one more "immediately obvious" you could stick `14-startm + 1` into a variable with a nice name like `writeColumn` and ditto for your `readColumn`. Then the next poor schmuck that comes along and has to decipher what was written will know what's going on without the burden of having to think. – JNevill Oct 15 '15 at 17:01
  • +1 just for poor schmuck. Yes and no. I personally hate deciphering variables and that would just add one more even if I agree it makes sense. Yea, I am demented XD – findwindow Oct 15 '15 at 17:04