Copy and Paste - Specific cells and range

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
771
Trying to copy a range to a specific range referencing cells, anyone know why this will work please?

Code:
Range("AG2:BC" & Range("BI1")).Copy _
Destination:=Sheets("Data").Range("D" & .Range("AD3"), .Range("Z" & .Range("AD4")))
 

Some videos you may like

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Do you have a With statement that you aren't showing? and what are in cells AD3 and AD4?
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,455
Office Version
  1. 2010
Platform
  1. Windows
I am not sure what you are trying to do but this code does at least compile and should show you what you need to do to fix your code
Code:
ii = ActiveSheet.Range("B1:B1")
With Worksheets("sheet3")


id3 = .Range("AD3")
id4 = .Range("AD4")


Range("A2:B" & ii).Copy Destination:=Sheets("Data").Range("D" & id3): .Range ("Z" & id4)
End With
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
771
Do you have a With statement that you aren't showing? and what are in cells AD3 and AD4?

No with statement

Its in a Worksheet_Calculate, AD3 is 9 and AD4 is 16 as they are variable.
Code:
Private Sub Worksheet_Calculate()
Range("AG2:BC" & Range("BI1")).Copy _
Destination:=Sheets("Data").Range("D" & .Range("AD3"), .Range("Z" & .Range("AD4")))
End Sub
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
771

ADVERTISEMENT

Works but gives me run-time error 438 after copying? Thanks.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Well the way you have written it .Range("AD3") and .Range("Z" & .Range("AD4") would need a With statement to go with the period/fullstop in front of Range.
What sheet are the 2 cells on?
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
771

ADVERTISEMENT

The two cells are on sheet ("Data")
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Then either
Code:
Private Sub Worksheet_Calculate()
    With Sheets("Data")
        Range("AG2:BC" & Range("BI1")).Copy _
                Destination:=.Range("D" & .Range("AD3"), .Range("Z" & .Range("AD4")))
    End With
End Sub
or
Code:
Private Sub Worksheet_Calculate()
    Range("AG2:BC" & Range("BI1")).Copy _
            Destination:=Sheets("Data").Range("D" & Sheets("Data").Range("AD3"), Sheets("Data").Range("Z" & Sheets("Data").Range("AD4")))
End Sub

and if you want you can remove the
Code:
Destination:=
if you want.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,297
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top