Problems selecting variable range in VBA

Sc0ut

New Member
Joined
Sep 24, 2014
Messages
6
Hello,

I'm really hoping someone can help me with this. I have been working on it all day and I can't seem to get it to work. I need to use variables to reference and copy a range of cells.

I'm getting the following error with this code that I have been working on:
"Run-time error '1004': Method 'Range' of object '_Worksheet' failed"

Here is my code:
Code:
Sub test()Dim StartrowC1 As String
Dim EndrowC1 As String
Dim C1Selection As String


StartrowC1 = (ActiveCell)
EndrowC1 = (ActiveCell.Offset(8, 0))
Let C1Selection = StartrowC1 & ":" & EndrowC1


'Selection.Copy
Sheet18.Range(C1Selection).Select


'and that's as far as I can get at the moment :(


End Sub

Please help! :eek:
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
StartrowC1 = ActiveCell.Address
EndrowC1 = ActiveCell.Offset(8).Address
Let C1Selection = StartrowC1 & ":" & EndrowC1
 

mohan.pandey87

Board Regular
Joined
Sep 14, 2012
Messages
146
Here you go:

Rich (BB code):
Sub test()


    Dim StartrowC1 As String
    Dim EndrowC1 As String
    Dim C1Selection As String
    
    StartrowC1 = (ActiveCell.Address)
    EndrowC1 = (ActiveCell.Offset(8, 0).Address)
    Let C1Selection = StartrowC1 & ":" & EndrowC1
    
    
    'Selection.Copy
    Sheet1.Range(C1Selection).Select
    
    
    'and that's as far as I can get at the moment :(




End Sub
 

Sc0ut

New Member
Joined
Sep 24, 2014
Messages
6
You're a genius! Thank you BOTH so much. I wish I had asked earlier! So much wasted time. I can't thank you enough.
 

Sc0ut

New Member
Joined
Sep 24, 2014
Messages
6

ADVERTISEMENT

Try

Code:
StartrowC1 = ActiveCell.Address
EndrowC1 = ActiveCell.Offset(8).Address
Let C1Selection = StartrowC1 & ":" & EndrowC1

Hello again,

I seem to be having that same problem with the next array of data to be copied. As this array only requires 4 separate cells to be copied, I'm trying to copy them to one variable that I would then paste from. Could this be the problem?

Error:
"Run-time error '1004': Method 'Range' of object '_Worksheet' failed"

Here is the code:
Code:
Sub test()
'Select data to be copied
Dim Cr1 As String
Dim Cr2 As String
Dim Cr3 As String
Dim Mrt As String
Dim MRSelection As String

'YEAR/MONITORING RESULTS
If Sheet1.Range("H17").Value = "" Then
Exit Sub
Else

'Prepare copy
Cr1 = ActiveCell.Offset(8, 0).Address
Cr2 = ActiveCell.Offset(8, 5).Address
Cr3 = ActiveCell.Offset(8, 10).Address
Mrt = ActiveCell.Offset(8, 15).Address
Let MRSelection = Cr1 & Cr2 & Cr3 & Mrt

'Execute copy and paste of selection
Sheet18.Range(MRSelection).Copy
Sheet1.Range("E26:H26").PasteSpecial (xlPasteValues)

End If

End Sub
Thanks in advance! :cool:
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Let MRSelection = Join(Array(Cr1, Cr2, Cr3, Mrt), ",")
 

Sc0ut

New Member
Joined
Sep 24, 2014
Messages
6

ADVERTISEMENT

Thank you! It works perfectly!

I'm am very grateful for you help. :rolleyes:
 
Last edited:

Sc0ut

New Member
Joined
Sep 24, 2014
Messages
6
One last thing if you can spare a second.

Is there any way to background the changing between sheets as it is processing the code? At the moment, when I activate the macro on the master sheet it takes me to a sheet that I don't want to see.

I have been looking into this and while it seems that you can make applications invisible (visible = false), the calling up of sheets in the same workbook seems to be harder to hide.

Very grateful for you help!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Application.Screenupdating=false
'your code here
applicationj.screenupdating=true
 

Watch MrExcel Video

Forum statistics

Threads
1,109,437
Messages
5,528,762
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top