Copy data and paste to last empty column

Hi All,

I created a spreadsheet which opens a temporary sheet to allow you copy a range of data and it sums the data and copu the sum into a column in new sheet, (sheets("Elec HH").range("B4")) in below code, and then delete the temporary sheet and open new temporary sheet if required. right now data will be paste only in Column B (starting from B4) of the new sheet. I am looking for a way to paste the data into the last empty column instead of replacing the existing data column. Any idea how can I change the below coding to copy these data?

Thanks alot

 Sub LoadButton()

    Dim lastRow As Long, rng As Range, cell As Range, lastcol As Range
    Dim waTemp As Worksheet
    Dim copydata As Long
    
    Set wsTemp = Sheets("TempElec")
    
    lastRow = Sheets("TempElec").Range("B1048576").End(xlUp).Row
    lastcol = Sheets("Elec HH").Cells(2, Columns.Count).End(xlUp).Column

    For r = 7 To lastRow
    
    Sheets("TempElec").Cells(r, "AX").Value = Application.WorksheetFunction.Sum(Range(Cells(r, "B"), Cells(r, "AW")))

    Next r

    'Copy and paste data
    With TempElec
      Sheets("TempElec").Range("AX7:AX" & Sheets("TempElec").Cells(Rows.Count, 49).End(xlUp).Row).Copy Sheets("Elec HH").Range("B4")
    End With
    
    '~~> Delete the temp sheet
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True

    '~~> Kill the temp file
    On Error Resume Next
    Kill TempPath & "TempChart.bmp"
    On Error GoTo 0

End Sub

Thanks for your reply.

I am getting an error "Application-defined or object-defined error"

Any idea why?

Originally Posted by Kamboj

Change line to

Sheets("TempElec").Range("AX7:AX" & Sheets("TempElec").Cells(Rows.Count, 49).End(xlUp).Row).Copy Sheets("Elec HH").Range("B") & Sheets("Elec HH").Cells(Rows.Count, "B").End(xlUp).Row

Maybe:

Sub hanify()
Dim x As Long
x = Cells(4, Columns.Count).End(xlToLeft).Column + 1
'CHANGE YOUR COPY DESTINATION FROM Sheets("Elec HH").Range("B4")
'To
'Sheets("Elec HH").cells(4, x)

End Sub

Thank you both. for some reason it does not properly. I have attached the spreadsheet could you have a look to see if you understand it?

if you click on button 1 the temporary sheet will appear. when it pops up if you just type any number and drag it to fill the table would be fine. many thanks

Thank you both so much with your helps I was managed to figure it out

You're welcome. Glad to help out and that you came up with a solution.