Copy from one workbook to another

Robert Germain

New Member
Joined
Mar 18, 2003
Messages
11
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.
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
Hi & welcome to MrExcel.
How about
Code:
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
 
Upvote 0
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?

Code:
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
 
Last edited by a moderator:
Upvote 0
When posting code please use code tags (the # icon in the reply window)

Try
Code:
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
 
Upvote 0
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
Code:
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
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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