Relative References

[Shockwave IIC]

2 in 1 day, go me......

I'm now trying to get a macros going for copy/pasting purposes that starts from whatever cell is selected on 1 particular worksheet

So a range of 44 rows deep and 87 columns across (I think)

Range("AU7:EB54").Select

That's what VBA gave me after recording what I highlighted dispite wanting to use Relative Referencing


What have I missed?

Also, any pit falls I should know about in regards to pasting it to a different worksheet?

 

[farmerscott]

Shockwave,

I am not sure why the macro recorder gave you that result, mostly it likes to give you relative referencing.

Give this code a try. I have put some comments in there to help.

Sub Copy_ActiveCell_Range()
'with active cell and the way you are coping/pasting you need to be very explicit as to worksheets.


Dim x As Long
Dim y As Long
Dim a As Long
Dim b As Long
Dim ws As Worksheet
Dim ws2 As Worksheet


x = 44 'change to suit number of rows to copy

y = 87 'change to number of columns to copy


Set ws = Sheets(1)
Set ws2 = Sheets(2)


With Sheets(1) 'we are being clear we are working with Sheet 1.


a = ActiveCell.Row 'collects the row number of the active cell
b = ActiveCell.Column 'same with the col number


ws.Range(Cells(a, b), Cells(a + x, b + y)).Copy 'create a range from the active cell and adds 44 rows and 87 cols.


ws2.Range("A1").PasteSpecial xlPasteValues 


End With


End Sub

Hope that helps,

FarmerScott

 

[Shockwave IIC]

Thanks for the reply

Unfortunately I don't have the spreadsheet with me at home and I appear to be weekend thick at the minute as I'm struggling to make sense of that.

I'll copy in the exact code in on Monday..

But, I'm looking something that will copy 44 rows and 87 columns with Row and Column 1 both being the cell that was selected (Worksheet "Rota") before the Macros is run. Then all of that is copied to B4 of "WorkTop" and a load of formatting happens. I've the Destination and Formatting working, it's just the source data not working.

Does that make sense?

 

[farmerscott]

Shockwave,

If I have read your 1st post right, the code allows you to put the active cell anywhere and the code copies 44 rows below and 87 cols to the right of the active cell.

I created the number of rows and cols as variables, so you can change them to suit your needs. The new code below now starts pasting (values only -no formatting or formulas) into Range("B4") of sheet "WorkTop".

Hope that helps,

Farmerscott.

Sub Copy_ActiveCell_Range()
'with active cell and the way you are coping/pasting you need to be very explicit as to worksheets.


Dim x As Long
Dim y As Long
Dim a As Long
Dim b As Long
Dim ws As Worksheet
Dim ws2 As Worksheet


x = 44 'change to suit number of rows to copy

y = 87 'change to number of columns to copy


Set ws = Sheets("Rota") 'change names of sheets to suit your circumstances.
Set ws2 = Sheets("WorkTop")


With ws 'we are being clear we are working with a particular sheet


a = ActiveCell.Row 'collects the row number of the active cell
b = ActiveCell.Column 'same with the col number


ws.Range(Cells(a, b), Cells(a + x, b + y)).Copy 'create a range from the active cell- Cells(a,b) and adds 44 rows and 87 cols.


ws2.Range("B4").PasteSpecial xlPasteValues 


End With


End Sub

 

[Shockwave IIC]

Thanks for this, got it working with little changes needed (The Pasting part of the code I had got working without issue)

For completion, this is what I ended with

Sub FourWeek()
'
' Macro1 Macro
'

'
    

Dim x As Long
Dim y As Long
Dim a As Long
Dim b As Long
Dim ws As Worksheet



x = 44
y = 85


Set ws = Sheets("Rota")



With ws


a = ActiveCell.Row
b = ActiveCell.Column

ws.Range(Cells(a, b), Cells(a + x, b + y)).UnMerge
ws.Range(Cells(a, b), Cells(a + x, b + y)).Copy

End With
    Sheets("Worktop").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.Zoom = 70
    Range( _
        "D4:D52,G4:G52,J4:J52,M4:M52,P4:P52,S4:S52,V4:V52,W4:W52,Z4:Z52,AC4:AC52,AF4:AF52,AI4:AI52,AL4:AL52" _
        ).Select
    Range("AL4").Activate
    Range( _
        "D4:D52,G4:G52,J4:J52,M4:M52,P4:P52,S4:S52,V4:V52,W4:W52,Z4:Z52,AC4:AC52,AF4:AF52,AI4:AI52,AL4:AL52,AO5:AO52,AO4,AR4:AR52,AS4:AS52,AV4:AV52,AY4:AY52,BB4:BB52" _
        ).Select
    Range("BB4").Activate
    Range( _
        "D4:D52,G4:G52,J4:J52,M4:M52,P4:P52,S4:S52,V4:V52,W4:W52,Z4:Z52,AC4:AC52,AF4:AF52,AI4:AI52,AL4:AL52,AO5:AO52,AO4,AR4:AR52,AS4:AS52,AV4:AV52,AY4:AY52,BB4:BB52,BE6:BE52,BH6:BH52,BK6:BK52,BN6:BO52" _
        ).Select
    Range("BN6").Activate
    Range( _
        "D4:D52,G4:G52,J4:J52,M4:M52,P4:P52,S4:S52,V4:V52,W4:W52,Z4:Z52,AC4:AC52,AF4:AF52,AI4:AI52,AL4:AL52,AO4:AO52,AR4:AR52,AS4:AS52,AV4:AV52,AY4:AY52,BB4:BB52,BE4:BE52,BH4:BH52,BK4:BK52,BN4:BO52,BR4:BR52,BU4:BU52,BX4:BX52,CA4:CA52,CD4:CD52,CG4:CG52" _
        ).Select
    Range("CG6").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.Zoom = 100
        Range("L4:O52,Z4:AC52,AN4:AQ52,BB4:BE52").Select
    Range("BB4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("D4:E52,H4:I52,R4:S52,V4:W52,AF4:AG52,AJ4:AK52,AT4:AU52,AX4:AY52").Select
    Range("AX4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Range("B4").Select
End Sub

 

[farmerscott]

Glad, you got it to work for you.