copying data to another workbook trouble with cell.offsets and lastrow+1

[B-Man]

Hi All,

I'm having an issue copying from one workbook to another. I know why but don't know how to fix it.
Its hard to explain but here goes... formula below

i loop through my sheet looking for entries that haven't been entered. when it finds one I want it to copy part of the data in a different order into another sheet
I have my row I want to copy certain cells and paste them into the destination workbook at the end of the data.
I'm using cell.offset to get the correct cells from the table but I cant seem to use that in the formula to copy it to the destination book and in the right layout...
im trying to "select" without selecting the last row+1 in Column A on the destination sheet then using offsets to find the other cells it needs to copy values to.

Sub Copy_loop()
'Application.ScreenUpdating = False

    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
     
     
    Set wbSource = ActiveWorkbook
    Set wsSource = wbSource.Worksheets("Sales")

    On Error Resume Next                                                 ' trys to open file from one link. if on another user it will fail so opens on next user
    workbooks.Open Environ("USERPROFILE") & ("\OneDrive\Shared - User1\-- Register --\Summary.xlsm")
    workbooks.Open Environ("USERPROFILE") & ("\OneDrive\Shared - User2\-- Register --\Summary.xlsm")
    On Error GoTo 0
    

    If Not wsSource Is Nothing Then
        Set wbDest = workbooks("Summary.xlsm")
        Set wsDest = wbDest.Worksheets("Input 2016+")

End If

'dont need to dim last row if i can just assign cell in formula?
'Dim Lastrow As Long
'     Lastrow = wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1)      ' Last Row +1


    For Each Cell In wsSource.Range("Table13[Paid]")
        If Cell.Value > 0 And Cell.Offset(0, 1).Value = "" Then
              Cell.Offset(0, 1).Value = "*"                             ' star cell to show it has been/will be entered



'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''need help from here..... this is the copy part that doesnt work''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    With wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1)             ' Find last row + 1 to paste data
    
'              (A) - Cell it need to go to in destination worksheet -  (worksheet source cell is offset from  ("Table13[Paid]"))
''.Range("A" & Rows.Count).End(xlUp).Offset(1) = wsSource.Cell.Offset(0, -7).Value
.Cell = wsSource.Cell.Offset(0, -7).Value

'              (D)
''Cell.Offset(0, 3).Value = wsSource.Cell.Offset(0, -1).Value

'              (M)
'Cell.Offset(0, 12).Value = wsSource.Cell.Offset(0, 2).Value &" - "  Cell.Offset(0, -6).Value ''''finalize combining cells

    End With

        End If
    Next Cell
    
'  workbooks("Summary.xlsm").Close savechanges:=True
  wbDest.Close savechanges:=True

        Application.ScreenUpdating = True

        wbSource.Activate

       MsgBox "Imported to Summary"

 

[B-Man]

I noticed the format was still incorrect. the negative numbers had the symbol in front of the number instead of the $ on the left hand side.
eg
$ -124.00
rather than
-$ 124.00
I tried reconfiguring the format but was having issues so I just created a macro in the summary sheet on open with the actual accounting number format and it works.
im sure if I played around enough I could get it to look the same but this works all the same.
it just looked weird against another column that was formatted as accounting.
Cheers,

Private Sub Workbook_Open()

Sheets("Input 2016+").Range("D5").EntireColumn.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub

 

[GWteB]

Thank you for the update. It's great that, despite the unruly behavior of Excel, you have still found a working solution. I say unruly, because I find it odd that using the Workbook Open event handler is apparently necessary to get the same view that was there when you saved the workbook to disk. No doubt you've seen that the format type as per your post #11 is identical to the second mentioned in my post #8, which generates a parenthetical display (!!??).
We will not be fooled by Excel. Eventually we find a solution. Well done!

 

[B-Man]

Thank you for the update. It's great that, despite the unruly behavior of Excel, you have still found a working solution. I say unruly, because I find it odd that using the Workbook Open event handler is apparently necessary to get the same view that was there when you saved the workbook to disk. No doubt you've seen that the format type as per your post #11 is identical to the second mentioned in my post #8, which generates a parenthetical display (!!??).
We will not be fooled by Excel. Eventually we find a solution. Well done!

yeah I figured even though it had the parenthetical if you select accounting via a macro that's what it shows yet displays correctly so I used it and works.