Solved: Copy from one sheet to multiple

[ukyank]

I create dthis based upon someone else's script on here and I keep getting a compile error. Was hoping someone might point me in the right direction as to what I am doing wrong. Cheers in advance

Sub CopyDates()
Dim AttendSheets As Variant, ggetDates As Worksheet
Dim attRow As Integer, attCol As Integer, ggetRow As Integer, ggetCol As Integer, n As Long
AttendSheet = Array("Sheet1", "Sheet2", "Sheet3")
Set ggetDates = Sheets("Data")
ggetRow = 2
ggetCol = 1
attRow = 2 'WILL REMAIN CONSTANT
attCol = 5
Application.ScreenUpdating = False 'Stop intermitten updates

For n = LBound(AttendSheet) To UBound(AttendSheet)
With AttendSheet(n)
'Column
Do While Not (IsEmpty(Cells(ggetRow, ggetCol)))
'Row
Do While Not (IsEmpty(Cells(ggetRow, ggetCol)))
Cells(attRow, attCol) = ggetDates.Cells(ggetRow, ggetCol)
ggetRow = ggetRow + 1
attCol = attCol + 1
Loop
ggetCol = ggetCol + 1
Loop
EndWith
Next n
Application.ScreenUpdating = True 'Restart update viewing

End Sub

[MWE]

you main problems are:
AttendSheet is not declared; you declare AttendSheets
You need a space between End and With The code below has no compile errors

Sub CopyDates()
Dim AttendSheets As Variant
Dim ggetDates As Worksheet
Dim attRow As Integer
Dim attCol As Integer
Dim ggetRow As Integer
Dim ggetCol As Integer
Dim n As Long

AttendSheets = Array("Sheet1", "Sheet2", "Sheet3")
Set ggetDates = Sheets("Data")
ggetRow = 2
ggetCol = 1
attRow = 2 'WILL REMAIN CONSTANT
attCol = 5
Application.ScreenUpdating = False 'Stop intermitten updates

For n = LBound(AttendSheets) To UBound(AttendSheets)
With AttendSheets(n)
'Column
Do While Not (IsEmpty(Cells(ggetRow, ggetCol)))
'Row
Do While Not (IsEmpty(Cells(ggetRow, ggetCol)))
Cells(attRow, attCol) = ggetDates.Cells(ggetRow, ggetCol)
ggetRow = ggetRow + 1
attCol = attCol + 1
Loop
ggetCol = ggetCol + 1
Loop
End With
Next n
Application.ScreenUpdating = True 'Restart update viewing

End Sub

[Paul_Hossler]

Suggest you add


Option Explicit


at the top of the module to require all variables to be Dim-ed

In the VBE, Tools, Editor tab, "Require Variable Declaration" should be checked to make new modules get the Option Explicit automatically

Paul

[ukyank]

Goodness I stink at this. Thanks very much to both of you for your help will definately use Option Explict going forward