if goto inside for loop

[gal12]

hello,

i'm trying to do an if go to fuction inside a for loop and i get an error.
please help me understand what i did wrong.

Sub CopyToMaster()
Dim destination As Worksheet
Dim emptyColumn As Long
Dim j As Long
Dim cell As Range
Set destination = Sheets("MASTER")
ShtCount = ActiveWorkbook.Sheets.Count
 
For i = 3 To ShtCount
        Worksheets(i).Activate
 
        If Range("C2,A7:J31").SpecialCells(xlCellTypeConstants).Select Is Nothing Then
            GoTo line2
            Else: GoTo line1
        End If
line1:
        Range("C2,A7:J31").SpecialCells(xlCellTypeConstants).Select
        emptyColumn = destination.Cells(1, i - 2).Column
            j = 0
            For Each cell In Selection
                destination.Cells(emptyColumn).Offset(j).Value = cell.Value
                j = j + 1
            Next cell
line2:
Next i
Worksheets("MASTER").Activate
End Sub

 

[Norie]

What are you trying to do with the code?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CopyToMaster()
   Dim destination As Worksheet
   Dim emptyColumn As Long
   Dim j As Long
   Dim cell As Range, Rng As Range
   Set destination = Sheets("MASTER")
   ShtCount = ActiveWorkbook.Sheets.Count
   
   For i = 3 To ShtCount
      With Worksheets(i)
         On Error Resume Next
         Set Rng = Range("C2,A7:J31").SpecialCells(xlCellTypeConstants)
         On Error GoTo 0
         If Not Rng Is Nothing Then
            emptyColumn = destination.Cells(1, i - 2).Column
            j = 0
            For Each cell In Rng
               destination.Cells(emptyColumn).Offset(j).Value = cell.Value
               j = j + 1
            Next cell
         End If
      End With
   Next i
   Worksheets("MASTER").Activate
End Sub

 

[gal12]

What are you trying to do with the code?

i have an excel with a lot of sheets that look the same, im trying to copy a matrix and paste it as a vector to one master spreadsheet each sheet as a new column

 

[gal12]

Hi & welcome to MrExcel.
How about

Sub CopyToMaster()
   Dim destination As Worksheet
   Dim emptyColumn As Long
   Dim j As Long
   Dim cell As Range, Rng As Range
   Set destination = Sheets("MASTER")
   ShtCount = ActiveWorkbook.Sheets.Count
  
   For i = 3 To ShtCount
      With Worksheets(i)
         On Error Resume Next
         Set Rng = Range("C2,A7:J31").SpecialCells(xlCellTypeConstants)
         On Error GoTo 0
         If Not Rng Is Nothing Then
            emptyColumn = destination.Cells(1, i - 2).Column
            j = 0
            For Each cell In Rng
               destination.Cells(emptyColumn).Offset(j).Value = cell.Value
               j = j + 1
            Next cell
         End If
      End With
   Next i
   Worksheets("MASTER").Activate
End Sub

didn't work unfortunately

 

[Fluff]

In what way?

 

[gal12]

In what way?

it ran but did nothing

 

[Fluff]

Oops, missed full stop, it should be

         Set Rng = .Range("C2,A7:J31").SpecialCells(xlCellTypeConstants)

 

[gal12]

i made this code and it ran fine until it found an empty spreadsheet in the middle. when i tried to add the if it stopped working.

Sub CopyToMaster()
Dim destination As Worksheet
Dim emptyColumn As Long
Dim j As Long
Dim cell As Range
Set destination = Sheets("MASTER")
ShtCount = ActiveWorkbook.Sheets.Count
 
 For i = 3 To ShtCount
        Worksheets(i).Activate
 
        Range("C2,A7:J31").SpecialCells(xlCellTypeConstants).Select

        emptyColumn = destination.Cells(1, i - 2).Column
            j = 0
            For Each cell In Selection
                destination.Cells(emptyColumn).Offset(j).Value = cell.Value
                j = j + 1
            Next cell
 Next i
Worksheets("MASTER").Activate
End Sub

 

[gal12]

Oops, missed full stop, it should be

         Set Rng = .Range("C2,A7:J31").SpecialCells(xlCellTypeConstants)

amazing! thank you so much!!!