VBA - copy range from one worksheet to another

[wibni]

Hi,

In Excel 2003 how can I copy a range from one worksheet to another using VBA

The code below gives me a type mismatch error.

For i = 1 To 11
    Worksheets.Add after:=Worksheets(Worksheets.Count), Count:=1
    Worksheets("Sheet2").Range("A1:D3") = Worksheets(Sheet1).Range("A1:D3")
Next i

 

[VoG]

Perhaps you mean like this

For i = 1 To 11
    Worksheets.Add after:=Worksheets(Worksheets.Count), Count:=1
    ActiveSheet.Range("A1:D3") = Worksheets("Sheet1").Range("A1:D3")
Next i

 

[wibni]

Thank you. I overlooked that.
The error is gone, however it still does not copy my range from Sheet1 to Sheet2.
What am I missing?

 

[VoG]

Sorry, it should be

ActiveSheet.Range("A1:D3").Value = Worksheets("Sheet1").Range("A1:D3").Value

 

[wibni]

Perfect, that worked.

Is it possible to just specify 1 cell in the target sheet and it just fills the columns and rows as needed?

The code below only copies 1 value across. I was hoping to use "A1" just as s reference where the paste should start from.

For i = 1 To 11
    Worksheets.Add after:=Worksheets(Worksheets.Count), Count:=1
    ActiveSheet.Range("A1").Value = Worksheets("Sheet1").Range("A1:D3").Value
Next i

 

[VoG]

Try

For i = 1 To 11
    Worksheets.Add after:=Worksheets(Worksheets.Count), Count:=1
    Worksheets("Sheet1").Range("A1:D3").Copy Destination:=ActiveSheet.Range("A1")
Next i

 

[wibni]

Brilliant. Thank you!