Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Formula not copying last row to new sheet

Posted on 2014-03-17
5
Medium Priority
?
267 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 2000 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

886 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