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.
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
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