Copy from one workbook to another

[Robert Germain]

I have a workbook where I need to copy data in a workbook (SAT_Marzo.xlsm) from B3 to the last cell containing data and paste this into a different workbook (CONTABILIDAD TOTAL 2018.xlsm) in the first non empty cell of column C.

This is the code I have presently where I am able to select the text and copy it to the first empty cell but this is in the same workbook (SAT_Marzo.xlsm).

Sub LastRowInOneColumn()
Dim LastRow As Long
With ActiveSheet
.Range("B3:B" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy
.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial
End With
End Sub


How can I modify this code to get my data copied correctly?

Thanks.

 

[WassimN]

With ActiveSheet
.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial
End With

Hello Robert

From the code you provided, you need to do the pasteSpaecial in the workbook and worksheet in CONTABILIDAD TOTAL 2018.xlsmso you need to FULLY qualify the line
With ActiveSheet

to point to the right destination in the right workbook.

Hope this helps.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Copy2OtherWbk()

   Dim Ws As Worksheet
   Set Ws = Workbooks("CONTABILIDAD TOTAL 2018.xlsm").Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   With ActiveSheet
      .Range("B3", .Range("B" & Rows.Count).End(xlUp)).Copy
   End With
   Ws.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
   
   
End Sub

Change the name in red to your sheet name

 

[Robert Germain]

Hi Fluff. Thanks for the greeting and your help.
I have amended the code and it now works. Howbeit I would need to insert some blank rows before the last line of the CONTABILIDAD TOTAL 2018.xlsm workbook and to be able to indicate in which worksheet I would like to some extra lines and/or the copied data. Where I have "Set Ws = Workbooks("CONTABILIDAD TOTAL 2018.xlsm").Sheets("MARZO")", could I change the .Sheets section have have a variable or something that would ask in which sheet I would like to perform this action?

Sub LastRowInOneColumn()

    Dim Ws As Worksheet
    Set Ws = Workbooks("CONTABILIDAD TOTAL 2018.xlsm").Sheets("MARZO")
    Dim LASTROW As Long
    With ActiveSheet
        .Range("B3:D" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy
        
    End With
     Ws.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
     Insert = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & Insert).Select
ActiveCell.Resize(5).EntireRow.Insert shift:=xlDown
    
End Sub

 

[Fluff]

When posting code please use code tags (the # icon in the reply window)

Try

Sub LastRowInOneColumn()

   Dim Ws As Worksheet
   Dim Sht As String
   Dim LASTROW As Long

   Sht = InputBox("Please enter sheet name")
   On Error Resume Next
   Set Ws = Workbooks("CONTABILIDAD TOTAL 2018.xlsm").Sheets(Sht)
   On Error GoTo 0
   If Ws Is Nothing Then
      MsgBox "Sheet " & Sht & " does not exist"
      Exit Sub
   End If
   With ActiveSheet
      .Range("B3:D" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy
   End With
   Ws.Range("B" & Rows.Count).End(xlUp).Offset(6, 0).PasteSpecial xlPasteValues
End Sub

 

[Robert Germain]

Awesome Fluff! Thanks for the tip on the tags and thanks a bunch for you assistance. Make my file and life easier!

When posting code please use code tags (the # icon in the reply window)

Try

Sub LastRowInOneColumn()

   Dim Ws As Worksheet
   Dim Sht As String
   Dim LASTROW As Long

   Sht = InputBox("Please enter sheet name")
   On Error Resume Next
   Set Ws = Workbooks("CONTABILIDAD TOTAL 2018.xlsm").Sheets(Sht)
   On Error GoTo 0
   If Ws Is Nothing Then
      MsgBox "Sheet " & Sht & " does not exist"
      Exit Sub
   End If
   With ActiveSheet
      .Range("B3:D" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy
   End With
   Ws.Range("B" & Rows.Count).End(xlUp).Offset(6, 0).PasteSpecial xlPasteValues
End Sub

 

[Fluff]

Glad to help & thanks for the feedback