Copy-paste range from existing file to destination file - VBA macro

Djani

Board Regular
Joined
Aug 26, 2015
Messages
61
Dear all,

I have a working VBA code that extracts data from the input file in the existing file, but I want to switch the logic.

Code:
Sub FillGER()
    'This part is for defining variables, don't change these!
    Dim ws                    As Worksheet
    Dim wbSource              As Workbook
    Dim lastRow               As Long
    Dim LR                    As Long
    Dim Filename              As String
    'The reference sheet of the active/open workbook
    Set ws = ActiveWorkbook.Worksheets("GERMANY C&D")
    'Full name of source file and pathlink --> opens file if it exists in the given folder
    Filename = "MOSYBASE GERMANY C&D.xlsx"
    Set wbSource = Workbooks.Open("I:\R&E Internal\01 Reporting & Tools\05 Pricing\01 Monthly Topics\01 VIVA\01 PC\03 FY16 ViVA\2. MOSYBASE 2.0\CENTER\" & Filename)
    'Extract data from source worksheet DATABASE from source file
    With wbSource.Worksheets("DATABASE")
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        'Copy data from sheet DATABASE to reference sheet
        'Macro calculates last row, starting from column A2:AE2 to last row
        .Range("A2:AE" & LR).Copy
    End With
    'Count the amount of rows in the reference sheet, pastes copied data in the LAST row
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    'Paste data in values of reference sheet
    ws.Range("A" & lastRow).PasteSpecial _
            Paste:=xlPasteValues, _
            operation:=xlPasteSpecialOperationNone, _
            skipblanks:=False, _
            Transpose:=False
    Application.CutCopyMode = False
    wbSource.Close False
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    'Remove entire row in every blank cell of column E in the Reference sheet
    On Error Resume Next
    ws.Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Sheets("Variable").Activate
End Sub

I want to have a macro that copy-pastes the data from the current file to the destination file with the following logic:
1. Select and copy range - starting from A4 to column M - from the current file ("QQ - EGB - NEW")
2. Paste range in the destination file ("Consolidation file") in the last row

If you need any additional information, please let me know

Djani
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try this

Adjust this line

Code:
       'Macro calculates last row, starting from column A2:AE2 to last row
        .Range("A2:AE" & LR).Copy

Change it to

Code:
        'Macro calculates last row, starting from column A4:M to last row
        .Range("A4:M" & LR).Copy

Complete code below

Code:
Sub FillGER()
    'This part is for defining variables, don't change these!
    Dim ws                    As Worksheet
    Dim wbSource              As Workbook
    Dim lastRow               As Long
    Dim LR                    As Long
    Dim Filename              As String
    'The reference sheet of the active/open workbook
    Set ws = ActiveWorkbook.Worksheets("GERMANY C&D")
    'Full name of source file and pathlink --> opens file if it exists in the given folder
    Filename = "MOSYBASE GERMANY C&D.xlsx"
    Set wbSource = Workbooks.Open("I:\R&E Internal\01 Reporting & Tools\05 Pricing\01 Monthly Topics\01 VIVA\01 PC\03 FY16 ViVA\2. MOSYBASE 2.0\CENTER\" & Filename)
    'Extract data from source worksheet DATABASE from source file
    With wbSource.Worksheets("DATABASE")
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        'Copy data from sheet DATABASE to reference sheet
        'Macro calculates last row, starting from column A4:M to last row
        .Range("A4:M" & LR).Copy
    End With
    'Count the amount of rows in the reference sheet, pastes copied data in the LAST row
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    'Paste data in values of reference sheet
    ws.Range("A" & lastRow).PasteSpecial _
            Paste:=xlPasteValues, _
            operation:=xlPasteSpecialOperationNone, _
            skipblanks:=False, _
            Transpose:=False
    Application.CutCopyMode = False
    wbSource.Close False
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    'Remove entire row in every blank cell of column E in the Reference sheet
    On Error Resume Next
    ws.Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Sheets("Variable").Activate
End Sub
 
Last edited:
Upvote 0
The current macro copies the data from file X and puts it in the active workbook. However, I want to copy data from the current workbook and paste it in under the last row of the target worksheet in the target workbook.
1. Current workbook = "QQ - EGB - NEW" / current sheet = "Inputsheet"
2. Target workbook name = "Opportunities consolidation file" / target sheet name = "Consolidation"

Simply changing the filenames in the current macro won't work (I tried that already). The logic has to be changed somehow, not only the range from A2:AE to A2:M -> can you help me change the entire macro which respects the mentioned criteria?
 
Upvote 0
Ok I apologize I did not fully understand your request. So you want to copy all the data from QQ - EGB - NEW sheet Inputsheet and paste it into the just below the last row in your Opportunities consolidation file sheet Consolidation.

What is the process you want, are you going to have both files open when it runs or should it open the file as it did in the previous macro?
Do you want the code to open the QQ - EGB - NEW file and copy the data, then open the Opportunities...and paste the data, or open one file and the other will already be open?

Is this file path correct "I:\R&E Internal\01 Reporting & Tools\05 Pricing\01 Monthly Topics\01 VIVA\01 PC\03 FY16 ViVA\2. MOSYBASE 2.0\CENTER" to open said file if the code is intended to open the file this path needs to be correct.

Just need a little more clarity on what you intend and I can help
 
Upvote 0
Dear Coding4Fun,

In the first part of your comment you describe exactly what I want. Ideally the macro should be used while having the current workbook (="QQ - EGB - NEW") open at all times. I want it to open the target file (="opportunities"), paste the data (coming from the current workbook (range = A4:M)) and close it.

The pathfile is correct as well!

Many thanks in advance,

Djani
 
Upvote 0
With the QQ -EGB - NEW workbook open when you run this it will open the Target workbook (opportunities) and then copy the specified range from QQ - EGB - NEW and past it just below the last row of data in the Opportunities file.

The following will do this but does not save or close either file, this can be done however based on your previous code I am not sure what you want to do with that

Code:
Sub Tester()

Dim ws                    As Worksheet
Dim wbSource              As Workbook
Dim lastRow               As Long
Dim LR                    As Long
Dim Filename              As String


Set ws = ActiveWorkbook.Worksheets("Inputsheet")


Filename = "Opportunities consolidation file.xlsx"
Set wbSource = Workbooks.Open("I:\R&E Internal\01 Reporting & Tools\05 Pricing\01 Monthly Topics\01 VIVA\01 PC\03 FY16 ViVA\2. MOSYBASE 2.0\CENTER\" & Filename)
ws.Activate


With ws
  wsRng = .Range("A" & .Rows.Count).End(xlUp).Row
 .Range("A4:M" & wsRng).Copy
End With


wbSource.Sheets("Consolidation").Activate


lastRow = wbSource.Sheets("Consolidation").Cells(Rows.Count, "A").End(xlUp).Row + 1


wbSource.Sheets("Consolidation").Range("A" & lastRow).PasteSpecial _
            Paste:=xlPasteValues, _
            operation:=xlPasteSpecialOperationNone, _
            skipblanks:=False, _
            Transpose:=False
Application.CutCopyMode = False


End Sub
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

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