asked on

VBA: copy & insert range dynamically based on config sheet v4

Hello Experts,

I have the following procedure from question 29016852 which allows me to copy and insert values from a source sheet to a destination sheet through a config sheet.

Please find attached example file:

-T4 is source sheet.
-ImportCC is destination sheet
-Config-Copy-ImportCC-T4 is config sheet

I am having some problems with the insert actions. Flag value = 2 reported in column G
Insert actions are performed in row 12 and 13 however they don't take into account the last row of source sheet which is row 11 as perform copy actions.
Insert actions should take into account last row of source sheet. In that case row 11 and not insert values above this row.
I highlightedunwanted rows.

Thank you in advance for your help.
Copy-Insert.xlsm

Martin Liss

Which button on which sheet is causing you the problem?

Luis Diaz

ASKER

Hello Martin,
My mistake I keep the additional sheets and procedure.
Please find bellow the revised version.

Procedure which is not working properly is Sub CopyRangeDynamically launched from CopyData button.
And in particular this condition part:

ElseIf wsConfig.Range("G" & rw) = 2 Then
Application.CutCopyMode = False ' to clear the clipboard
'

Insert
                'wsDestination.Activate
                MaxRowDestinationSheet = wsSourceSheet.UsedRange.Rows.Count + wsSourceSheet.UsedRange.Rows(1).Row - 1
                wsDestination.Range(Cells(wsConfig.Range("F" & rw) + 1, iDestinationColumn), Cells(MaxRowDestinationSheet, iDestinationColumn)) = wsConfig.Range("H" & rw)
                Application.CutCopyMode = False ' to clear the clipboard
        'MsgBox MaxRowDestinationSheet
            End If

Open in new window

Thank you very much for your help.
Copy-Insert-V2.xlsm

Martin Liss

In the second workbook you posted there are no highlighted rows that I can see but that's OK. When you say

...and not insert values above this row.

do you mean that new rows should be added after row 11?

Luis Diaz

ASKER

You are right, please find attached the revised version with unwanted rows highlighted.
I don't want that the insert actions add rows bellow the last row of source sheet..
Insert actions should take into account the last row of copy actions in that case row 11 I don't understand why they are values inserted in row 12 and row 13. This should not be the case.

Thank you for your help.
Copy-Insert-V3.xlsm

Martin Liss

In which row on the ImportCC sheet do you want to put the data from row 10 of the Config-Copy-ImportCC-T4 sheet?

Luis Diaz

ASKER

Insert actions should be in line with the number of rows of source sheet.
In this example source sheet T4 contains 11 rows. Copy actions perform actions for 11 rows -1 (header). Insert actions should also perform 11 rows -1 (header) and not for 13 rows.

As you can see in the code insert actions is done by the following condition

Application.CutCopyMode = False ' to clear the clipboard
            ElseIf wsConfig.Range("G" & rw) = 2 Then
                Application.CutCopyMode = False ' to clear the clipboard
  'Insert
                'wsDestination.Activate
                MaxRowDestinationSheet = wsSourceSheet.UsedRange.Rows.Count + wsSourceSheet.UsedRange.Rows(1).Row - 1
                wsDestination.Range(Cells(wsConfig.Range("F" & rw) + 1, iDestinationColumn), Cells(MaxRowDestinationSheet, iDestinationColumn)) = wsConfig.Range("H" & rw)
                Application.CutCopyMode = False ' to clear the clipboard
        'MsgBox MaxRowDestinationSheet
            End If

Open in new window

I don't understand why it doesn't take the last used range of source sheet which should be row 11.
as we have: MaxRowDestinationSheet = wsSourceSheet.UsedRange.Rows.Count + wsSourceSheet.UsedRange.Rows(1).Row - 1

Thank you again for your help.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

You are right the last used range of source sheet T4 is row 13 however I cannot see where are located, probably there are leading spaces or something like this? How to get ride of those rows through VBA?

What I am going to do is to report the following for Insert actions:

'Insert

wsDestination.Range(Cells(wsConfig.Range("F" & rw) + 1, iDestinationColumn), Cells(MaxRowSourceSheet, iDestinationColumn)) = wsConfig.Range("H" & rw)
Application.CutCopyMode = False ' to clear the clipboard

By doing so I will be sure that the insert actions is based on last used range of source sheet.

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Yes, Thank you very much for your help.

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
Experts Exchange Top Expert VBA (current)

VBA: copy &amp; insert range dynamically based on config sheet v4

VBA: copy & insert range dynamically based on config sheet v4