VBA set dim range in different sheet

[Tigretto]

Hi,

So i'm trying to set a dim which helps me to copy some rows. I'm new with this and build the code with the help of another post on this forum. One problem with this code is that i need to activate the sheet, which i do not want to do. I've managed this with the .copy part. How do i fix this with the LastRow = Range("D11") part?

  Sheets("Achter de schermen").Activate
  Dim LastRow
  LastRow = Range("D11").Value
  Range("B13:G" & LastRow).Copy
 
  Sheets("Spelers").Range("A3").Insert Shift:=xlDown
  Sheets("Spelers").Range("A3").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

I've tried this:

  Sheets("Achter de schermen").Activate
  Dim LastRow
  LastRow = Sheets("Achter de schermen").Range("D11").Value
  Range("B13:G" & LastRow).Copy
 
  Sheets("Spelers").Range("A3").Insert Shift:=xlDown
  Sheets("Spelers").Range("A3").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

This doesn't work. Does anyone know how to set the range without switching to the "Achter de schermen" sheet?

 

[Fluff]

Hi & welcome to MrExcel.
How about

  Dim LastRow
  With Sheets("Achter de schermen")
      LastRow = .Range("D11").Value
      .Range("B13:G" & LastRow).Copy
  End With
  Sheets("Spelers").Range("A3").Insert Shift:=xlDown
  Sheets("Spelers").Range("A3").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

 

[Tigretto]

Thanks, this works great. So if i understand correctly the With is used to say "we're doing this in this sheet", is it? Could you refer me to some information about how to use the With element?

 

[Fluff]

the With is used to say "we're doing this in this sheet", is it?

Yes that's right.

VBA With Statement (With - End With)

You can use "With" statement to specify an object for once and then run multiple statements for it. It can help you simplify the complex code.

excelchamps.com

 

[Tigretto]

Thanks a lot. So could someone tell me what i'm doing wrong here?

Sub TeamToevoegen()
'
' TeamToevoegen Macro
'

'
  Application.ScreenUpdating = False
  Sheets("Teams").Activate
  Range("A4").EntireRow.Insert
  Sheets("Achter de schermen").Range("A8:C8").Copy
  Sheets("Teams").Range("A4").PasteSpecial Paste:=xlPasteValues
  Sheets("Teams").Range("A4").Select
  Sheets("Spelers").Activate
  Range("A3").EntireRow.Insert
 

  Dim LastRow
  With Sheets("Achter de schermen")
      LastRow = .Range("D11").Value
      .Range("B13:G" & LastRow).Copy
  End With
 
  Sheets("Spelers").Range("A3").Insert Shift:=xlDown
  Sheets("Spelers").Range("A3").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True
  Sheets("Spelers").Range("A3").Select
 
    Dim LastRowTwo
  With Sheets("Achter de schermen")
      LastRowTwo = .Range("D22").Value
      .Range("B23:G" & LastRowTwo).Copy
  End With
 
  Sheets("CSV Spelers").Range("A1").Insert Shift:=xlDown
  Sheets("CSV Spelers").Range("A1").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True
  Sheets("CSV Spelers").Range("A1").Select
 
  Sheets("Achter de schermen").Activate
  Range("B5").Select
  ActiveCell.FormulaR1C1 = "0"
 
  Sheets("Team toevoegen").Activate
  Range("B8:C14").Select
  Selection.ClearContents
 
  Range("C2").Select
  Selection.ClearContents
 
  Application.CutCopyMode = False
 
 
  
End Sub

It says the problem is in this line, but I can't find it.

.Range("B23:G" & LastRowTwo).Copy

 

[diddi]

check what is in D22. if it is empty or has anything but a number in it, then it will error