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

B-Man

Board Regular
Joined
Dec 29, 2012
Messages
86
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.



VBA Code:
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

Board Regular
Joined
Dec 29, 2012
Messages
86
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,

VBA Code:
Private Sub Workbook_Open()

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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,830
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Dec 29, 2012
Messages
86
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.
 

Forum statistics

Threads
1,141,282
Messages
5,705,483
Members
421,398
Latest member
Rahat Anwar

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
Top