1004 Error at the end

[Kevineamon]

Hi guys
I'm getting a 1004 error with the following code. I've just looked a second ago at this variable = Lastcell.
I used a message box and it seems to be empty.
Any advise would be great guys. My brain is killing me here.

Thanks

Option Explicit
    
Sub FullFatGen()




Dim AdminWorkbookName As String, LoopSwitchBoardName As String, GetBook As String, ListSheetname As String
Dim MyFileName As String, SwitchBoardName As String
Dim CurrentWB As Workbook, TempWB As Workbook, TempSheet As Worksheet
Dim iLoop As Integer, BeginCell As Integer, CellPaste As Integer


      
  Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Tempo"
           
           
    'Creates New workbook/pastes sheet data
    'Set TempWB = Application.Workbooks.Add(1)
                            
iLoop = 0
BeginCell = 5
CellPaste = 0
    
    Do Until ThisWorkbook.Worksheets("Lists").Range("CP5").Value = iLoop
            
        LoopSwitchBoardName = ThisWorkbook.Worksheets("Lists").Range("CI" & BeginCell).Value
            
        Call TransferToTemp(LoopSwitchBoardName, GetBook, CellPaste)
        
        iLoop = iLoop + 1
        BeginCell = BeginCell + 1
        CellPaste = CellPaste + 1
                            
    Loop
             
End Sub


Public Sub TransferToTemp(LoopSwitchBoardName As String, GetBook As String, CellPaste As Integer)
Dim TempSheet As Worksheet
Dim CountOneSheet As Integer
Dim LastCell As Range
Dim LastCellColRef As Long


LastCellColRef = 1  'column number to look in when finding last cell


ThisWorkbook.Worksheets("Tempo").Activate
Set LastCell = Sheets("Tempo").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)


ActiveWorkbook.Worksheets(LoopSwitchBoardName & "_SAT").UsedRange.Copy


With ThisWorkbook.Worksheets("Tempo").Cells(LastCell)
.PasteSpecial Paste:=xlPasteValues


End With


End Sub

 

[Fluff]

Try replacing this

With ThisWorkbook.Worksheets("Tempo").Cells(LastCell)
.PasteSpecial Paste:=xlPasteValues


End With

with

LastCell.PasteSpecial paste:=xlPasteValues

 

[Kevineamon]

Fluff you're a star man.... Love ya

 

[Fluff]

You're welcome & thanks for the feedback