help with VBA code (selecting ranges in different worksheets)

dirtywizard

New Member
Joined
May 1, 2015
Messages
32
Hi all,

I've been playing with coding and designed the below:


Code:
Sub codetest()
Dim LR As Integer
Range("c2").Select
ActiveCell.FormulaR1C1 = "=weeknum(rc[1],2)"
LR = Range("d" & Rows.Count).End(xlUp).Row
Range("c2").Select
Selection.AutoFill Destination:=Range("c2:c" & LR), Type:=xlFillDefault


Dim LR2 As Integer
Range("b2").Select
ActiveCell.FormulaR1C1 = "=weekday(r[1]c[2])"
LR2 = Range("d" & Rows.Count).End(xlUp).Row
Range("b2").Select
Selection.AutoFill Destination:=Range("b2:b" & LR2), Type:=xlFillDefault


Dim LR3 As Integer
Range("a2").Select
ActiveCell.FormulaR1C1 = "=r[1]c[3]"
LR3 = Range("d" & Rows.Count).End(xlUp).Row
Range("a2").Select
Selection.AutoFill Destination:=Range("a2:a" & LR3), Type:=xlFillDefault


Which does exactly what it needs to (pulls apart column D which is a mixture of date and time entries into three columns with date, day of the week and week of the year on every row where there is a value in column D), but I want to put a button on my front sheet (sheet1) that runs the macro in sheet 2. I thought I'd try the code below on just one of the columns and got a 400 error.


Code:
Sub refresh()
Dim LR As Integer
Worksheets("sheet2").Range("c2").Select
ActiveCell.FormulaR1C1 = "=weeknum(rc[1],2)"
LR = Worksheets("sheet2").Range("d" & Rows.Count).End(xlUp).Row
Worksheets("sheet2").Range("c2").Select
Selection.AutoFill Destination:=Worksheets("sheet2").Range("c2:c" & LR), Type:=xlFillDefault


End Sub

I assume I've messed up the references and didn't want to proceed further until I'd nailed it: what have I done wrong?

Thanks all
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
dirtywizard,

Your error is caused by the line

Code:
Worksheets("sheet2").Range("c2").Select

You need to select or activate the sheet before you can select a range within the sheet. Then you must qualify the range selection in some way, so you could replace with

Code:
Sheets("sheet2").Activate
Sheets("Sheet2").Range("c2").Select
'or
ActiveSheet.Range("c2").Select

You could try the following code which does not require you to be 'selecting' anything.

Code:
Sub refresh2()
Dim LR As Integer
With Worksheets("sheet2")
LR = .Range("d" & Rows.Count).End(xlUp).Row
.Range("a2:a" & LR).FormulaR1C1 = "=r[1]c[3]"
.Range("b2:b" & LR).FormulaR1C1 = "=weekday(r[1]c[2])"
.Range("c2:c" & LR).FormulaR1C1 = "=weeknum(rc[1],2)"
End With
End Sub

Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,203,619
Messages
6,056,325
Members
444,860
Latest member
Daz511

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top