Solved

Formula not copying last row to new sheet

Posted on 2014-03-17
5
251 Views
Last Modified: 2014-03-18
I did not create this code.  It is copying data from IO Staging to Inbound, but counting rows on Variances Tab.  It was not copying the last row of the IO Staging tab and thus not copying it to the Inbound tab.  

I changed this row to say +3 instead of +2 and it seems to work
    lRow = Sheets("Variances").Range("A" & Rows.Count).End(xlUp).Row + 3

Can someone tell me if that was what I needed to do and if so why.




---------------------------------------------------------------------------

  Sheets("Inbound").Select
   
    lRow = Sheets("Variances").Range("A" & Rows.Count).End(xlUp).Row + 3
    zrow = Sheets("Variances").Range("A" & Rows.Count).End(xlUp).Row
   
    Sheets("Inbound").Range("A6:A" & lRow).Value = "A"
    Sheets("Inbound").Range("C6:C" & lRow).Value = Sheets("IO Staging").Range("C3:C" & zrow).Value
0
Comment
Question by:leezac
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39935164
It's strange that you're getting your row numbers from the Variances sheet rather than the IO Staging sheet you are copying from:  


lRow = Sheets("IO Staging").Range("A" & Rows.Count).End(xlUp).Row + 3
zrow = Sheets("IO Staging").Range("C" & Rows.Count).End(xlUp).Row
   
Sheets("Inbound").Range("A6:A" & lRow).Value = "A"
Sheets("Inbound").Range("C6:C" & lRow).Value = Sheets("IO Staging").Range("C3:C" & zrow).Value
0
 

Author Comment

by:leezac
ID: 39935171
I agree
0
 

Author Comment

by:leezac
ID: 39936872
What did changing this number accomphish?

Row + 3
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 500 total points
ID: 39936928
You're copying a range that goes from c3 to zrow into a range that starts at c6. The +3 extends the destination range and makes  it fit. Without it, you're copying n rows into n-3 rows.
0
 

Author Closing Comment

by:leezac
ID: 39936987
Great response and help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question