problem with selecting a range

[kbishop94]

This is on a worksheet. I am using a command button to select (I'm trying to select!) a range on another worksheet and paste it onto the worksheet where the command button is.

THIS works:

ActiveWorkbook.Worksheets("Seatex Incident Log").Cells(18, 6).Select
Selection.Copy

THESE do NOT:

ActiveWorkbook.Worksheets("Seatex Incident Log").Cells(18, 18), Cells(707, 18).Select
Selection.Copy

ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(18, 18), Cells(rCol, 18)).Select

the code works for selecting and copying a single cell, but not for a range.

 

[Fluff]

Try

With Worksheets("Seatex Incident Log")
   .Range(.Cells(18, 18), .Cells(rCol, 18)).Copy
End With

 

[DanteAmor]

I guess rCol is a column number, then Try this

Sub test()
  Dim rcol As Long, sh As Worksheet
  rcol = 20
  Set sh = Sheets("Seatex Incident Log")
  sh.Range(sh.Cells(18, 18), sh.Cells(18, rcol)).Copy
End Sub

 

[kbishop94]

Try

With Worksheets("Seatex Incident Log")
   .Range(.Cells(18, 18), .Cells(rCol, 18)).Copy
End With

Thank you, Fluff. Thats got it.

 

[kbishop94]

I guess rCol is a column number, then Try this

Sub test()
  Dim rcol As Long, sh As Worksheet
  rcol = 20
  Set sh = Sheets("Seatex Incident Log")
  sh.Range(sh.Cells(18, 18), sh.Cells(18, rcol)).Copy
End Sub

My bad. I its actually the number of rows (I've been trying so many different things to try and get this, and I was just really getting frustrated over something this simple.) (its been a really crappy monday so far. )

As always, thanks for the help.

 

[Fluff]

You're welcome & thanks for the feedback

 

[kbishop94]

What would be the best way to do the same thing, but for multiple columns instead of just the "R" column?

I'm trying to get only columns: B, E, F, G, K and R all starting at row #18 and down to the last row copied over to the same worksheet that I did with the above code.

I can modify your code to start at column B (2) and go over to R (18), copy all of them over, and then continue with the code where it just deletes the columns in between that I dont need... but unfortunately most of those unwanted columns contain ALOT of data and I'm trying to avoid any unnecessary lagging of the code (especially since I dont need the data thats in those other columns.) Thanks for any suggestions and assistance.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[kbishop94]

On second thought, I just tried it with copying over ALL the data and I cant notice any lag at all. I think it will be fine with just copying it all over and then I'll just use the data that I need. Thanks!

 

[DanteAmor]

B, E, F, G, K and R all starting at row #18 and down to the last row copied

Try this

Sub test()
  Dim lr As Long, sh As Worksheet
  Set sh = Sheets("Seatex Incident Log")
  lr = sh.Range("B" & Rows.Count).End(xlUp).Row
  sh.Range("B18:B" & lr & ",E18:G" & lr & ",K18:K" & lr & ",R18:R" & lr).Copy
End Sub