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
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Which button on which sheet is causing you the problem?
0
LD16Author Commented:
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
0
Martin LissOlder than dirtCommented:
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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LD16Author Commented:
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
0
Martin LissOlder than dirtCommented:
In which row on the ImportCC sheet do you want to put the data from row 10 of the Config-Copy-ImportCC-T4 sheet?
0
LD16Author Commented:
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.
0
Martin LissOlder than dirtCommented:
In the line MaxRowDestinationSheet = wsSourceSheet.UsedRange.Rows.Count + wsSourceSheet.UsedRange.Rows(1).Row - 1 you are adding wsSourceSheet.UsedRange.Rows.Count which is 13 and wsSourceSheet.UsedRange.Rows(1).Row - 1 which is always 0 to get a total of 13 for the row. BTW the reason the latter part of that gives zero is that wsSourceSheet.UsedRange.Rows(1).Row says "what is the last row of row 1" and that of course is always 1 and so subtracting 1 gives 0.

If you want row 10 to go to row 10 and 11 to 11, etc then you can change that line to

MaxRowDestinationSheet = rw

or just delete the line and use rw directyly in place of MaxRowDestinationSheet in the next line.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
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.
0
Martin LissOlder than dirtCommented:
Excel is not really very good at determining the last row in human terms. The most accurate and efficient way to determine the last row that I know of is wsSourceSheet.Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row which will give 11.

Is your problem now fixed?
0
LD16Author Commented:
Yes, Thank you very much for your help.
0
Martin LissOlder than dirtCommented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.