1004 Error at the end

Kevineamon

New Member
Joined
Aug 1, 2018
Messages
27
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

Code:
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
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Try replacing this
Code:
With ThisWorkbook.Worksheets("Tempo").Cells(LastCell)
.PasteSpecial Paste:=xlPasteValues


End With
with
Code:
LastCell.PasteSpecial paste:=xlPasteValues
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top