Copy/paste values if the cell next to it is empty every month

Mortenhoey

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Is there a way i can copy paste specific rows to the excel ark. The things i need:

- copy paste e3, e4, e9, e13 and e15 from mappe2 (book) to mappe 1 (book)
- has to copy the value in the given row if the cell next to it is empty
- the macro has to be applied from mappe1 (book)
- next month the may values (which is coming, later at the month) and run it again so it is the may values which copies to mappe1 (book)
- The workbooks has to be open

So the point is to always copy paste the lastest value from mappe2 (book) to mappe 1 - month by month.

In mappe1 there are maybe more variables to come to maybe in two months there has to e3,e4,e9,e13,e15 and add e5 and e14.

So if I can put more rows in the macro for future months copy paste it would be perfect.

I really can figure out to make the macro for choosing a value if the next cell is empty and the same when pasting it in mappe1 (book)

Link to: Photo

Thank you for the help.
 

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.

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Try this in a standard module:
VBA Code:
Sub GetValuesFromMappe2()
    Dim src As Workbook, Canceled As Boolean, i As Long, j As Long, curData() As String, msg As Long, newCodes As String
    Dim fnd As Range, Updated As Boolean
   
    'OPTIMIZE MACRO SPEED
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'GET THE SOURCE FILE
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select the source file"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel files", "*.xls*"
        If .Show <> -1 Then
            Canceled = True
            GoTo ResetSettings
        End If
        Set src = Workbooks.Open(Filename:=.SelectedItems(1))
    End With
   
    'GET THE eX CODES CURRENTLY ON Mappe1
    With ThisWorkbook.Worksheets("Ark1")
        For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
            If i = 2 Then
                ReDim curData(j)
                curData(j) = .Cells(i, "A")
                j = j + 1
            Else
                If IsInArray(.Cells(i, "A"), curData) = False Then
                    ReDim Preserve curData(j)
                    curData(j) = .Cells(i, "A")
                    j = j + 1
                End If
            End If
        Next i
    End With
   
    'CHECK CODES TO GET VALUES FOR
CheckCodes:
    msg = MsgBox("Would you like to retrieve the data for the following codes? Press Yes if you'd like to proceed, No if you'd like to add more codes, Cancel if " & _
                                "you'd like to cancel the procedure." & vbCrLf & vbCrLf & "Codes:" & vbCrLf & Join(curData, ", "), vbInformation + vbYesNoCancel, "Check Codes")
    If msg = vbYes Then
        GoTo Output
    ElseIf msg = vbCancel Then
        Canceled = True
        GoTo ResetSettings
    'If new codes are to be added
    ElseIf msg = vbNo Then
AddCodes:
        'Type in new codes
        newCodes = Application.InputBox("Type codes to add (separate each code by a comma with no space).", "Add Codes", Type:=2)
       
        'If InputBox is blank
        If newCodes = "False" Then
            msg = MsgBox("No codes specified. Would you like to proceed without adding new codes?", vbExclamation + vbYesNoCancel, "Caution")
            If msg = vbYes Then
                GoTo Output
            ElseIf msg = vbNo Then
                GoTo AddCodes
            ElseIf msg = vbCancel Then
                Canceled = True
                GoTo ResetSettings
            End If
        End If
       
        'If InputBox is filled
        'Check if the added codes exist on Mappe2
        For i = LBound(Split(newCodes, ",")) To UBound(Split(newCodes, ","))
            Set fnd = src.Worksheets("Ark1").Range("A:A").Find(Split(newCodes, ",")(i), , xlValues, xlWhole)
            If fnd Is Nothing Then
                MsgBox "The code " & Split(newCodes, ",")(i) & " was not found on Mappe2.", vbExclamation, "Error"
                GoTo AddCodes
            Else
                Set fnd = Nothing
            End If
        Next i
        'Update the codes to add
        For i = LBound(Split(newCodes, ",")) To UBound(Split(newCodes, ","))
            If IsInArray(Split(newCodes, ",")(i), curData) = False And Split(newCodes, ",")(i) <> "" Then
                ReDim Preserve curData(j)
                curData(j) = Split(newCodes, ",")(i)
                j = j + 1
            End If
        Next i
        Updated = True
        GoTo CheckCodes
       
    End If
   
Output:
    'OUTPUT VALUES
    If Updated Then
        curData = SortArray(curData)
    End If
    With ThisWorkbook.Worksheets("Ark1")
        For i = LBound(curData) To UBound(curData)
            .Cells(i + 2, "A") = curData(i)
            Set fnd = src.Worksheets("Ark1").Range("A:A").Find(curData(i), , xlValues, xlWhole)
            src.Worksheets("Ark1").Range(Cells(fnd.Row, "B"), Cells(fnd.Row, Columns.Count).End(xlToLeft)).Copy .Cells(i + 2, "B")
        Next i
       
    End With
   
ResetSettings:
    'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    If Canceled Then
        MsgBox "Precedure canceled."
    Else
        MsgBox "Precedure completed."
    End If
   
End Sub

Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
    Dim element As Variant
    On Error GoTo IsInArrayError 'array is empty
        For Each element In arr
            If element = valToBeFound Then
                IsInArray = True
                Exit Function
            End If
        Next element
Exit Function
IsInArrayError:
    On Error GoTo 0
    IsInArray = False
End Function

Function SortArray(myArray As Variant)
    Dim i As Long, j As Long, Temp As Variant
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
            If CLng(Mid(myArray(i), 2, Len(myArray(i)) - 1)) > CLng(Mid(myArray(j), 2, Len(myArray(i)) - 1)) Then
                Temp = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = Temp
            End If
        Next j
    Next i
    SortArray = myArray
End Function
 
Last edited:

Mortenhoey

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Where do i define which row I want to copy from and paste? :)

looks really good
 

Mortenhoey

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
It’s the get Excode and output values right? Row “a”? :)
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

After selecting the source workbook, press No on the message box and type in the codes for which you need data.
In the image you provide in #1 for instance, you have e3, e4, e9, e13, and e15.
The data for these are automatically retrieved.
If you need e1 and e2 additionally, type in "e1,e2" to the input box that shows up after selecting the source workbook.
 

Mortenhoey

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Is it possible to make me choose all the rows because sometime some months fx e3 isnt necessary. So if it’s possible to have no values transfer automatic and I can choose which row I need in the given month.
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That's another story... but it seems that you mentioned it in #5, sorry.
I think I'll write another code but for now I'll go to bed. Maybe a new code will be posted later.
 

Mortenhoey

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Just to summarize

it basically just is a copy/paste function between two workbooks. Where i have to choose the location row in one workbook and the detonation row in another.

then the macro has to copy the value in the given row (last cell with values - next to it is an empty row) and paste it in the other workbook in a given row (next to the last cell with value) the empty cell.
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Sub GetValuesFromMappe2Mod()
    Dim src As Workbook, fName As String, srcWs As Worksheet, desWs As Worksheet, Canceled As Boolean, looped As Boolean, copyTar As Range, cell As Range, i As Long

    'GET THE SOURCE FILE
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select the source file"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel files", "*.xls*"
        If .Show <> -1 Then
            Canceled = True
            GoTo ResetSettings
        End If
        Set src = Workbooks.Open(Filename:=.SelectedItems(1))
        fName = Replace(.SelectedItems(1), src.Path & "\", "")
    End With
    
    'SET SHEET VARIABLES
    Set srcWs = src.Worksheets("Ark1")
    Set desWs = ThisWorkbook.Worksheets("Ark1")
    
    'SELECT ROWS TO COPY
    srcWs.Activate
SelectTarget:
    If looped Then
        MsgBox "Invalid selection", vbExclamation, "Error"
        looped = False
    End If
    On Error GoTo TargetNotSelected
    Set copyTar = Application.InputBox("Select cells in column A to copy", "Select", Type:=8)
    On Error GoTo 0
    GoTo TargetSelected
TargetNotSelected:
    Canceled = True
    GoTo ResetSettings
TargetSelected:
    If copyTar Is Nothing Then
        Canceled = True
        GoTo ResetSettings
    Else
        If copyTar.Worksheet.Parent.Name = fName And copyTar.Worksheet.Name = "Ark1" And RangeIsWithinRange(copyTar, Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))) Then
            'Do nothing and proceed
        Else
            looped = True
            GoTo SelectTarget
        End If
    End If
    desWs.Activate
    
    'OPTIMIZE MACRO SPEED
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'OUTPUT VALUES
    desWs.Cells.ClearContents
    For i = 1 To 12
        desWs.Cells(1, i + 1) = MonthName(i)
    Next i
    i = 2
    For Each cell In copyTar.Cells
        srcWs.Range(cell, srcWs.Cells(cell.Row, Columns.Count).End(xlToLeft)).Copy desWs.Cells(i, "A")
        i = i + 1
    Next cell

ResetSettings:
    'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    If Canceled Then
        MsgBox "Precedure canceled."
    Else
        MsgBox "Precedure completed."
    End If
    
End Sub

Function RangeIsWithinRange(rng1 As Range, rng2 As Range) As Boolean
    Dim i As Long, cell As Range
    RangeIsWithinRange = True
    For Each cell In rng1.Cells
        If Intersect(cell, rng2) Is Nothing Then
            RangeIsWithinRange = False
            Exit Function
        End If
    Next cell
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,764
Members
418,411
Latest member
Excellency

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