Solved

Excel- Copy amounts from one tab to another

Posted on 2013-06-05
10
170 Views
Last Modified: 2013-06-07
Hi,
I have a worksheet with labels starting on row 10 column F and totals for each of these labels on rows  staring on Column F to column YJ rows 701 to 704. The labels are company names i.e. X1, X2, X3 etc. How can I copy all the labels who's rows 701 to 704 are not all equal to zero into a new tab named sheet1 starting on row 4 column 3? See sample below on my current worksheet I have X1, X2, and X3. On what I want X2 is not copied because rows 701-704 are all zero.

Current                              Want      
      Column F                              
Row 10      X1      X2      X3            X1      X3
                                    
Row 701      100      0      0            100      0
Row 702      0      0      0            0      0
Row 703      0      0      0            0      0
Row 704      0      0      50            0      50

conernesto
0
Comment
Question by:Conernesto
  • 6
  • 4
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
This seems to be working for me:

Sub CopyColumns()
    
    Const StartCol As Long = 6
    Const StopCol As Long = 660
    Const StartRow As Long = 701
    Const StopRow As Long = 704
    Const HeaderRow As Long = 10
    
    Dim DestCol As Long
    Dim SourceWs As Worksheet
    Dim DestWs As Worksheet
    Dim Counter As Long
    Dim rng As Range
    
    Set SourceWs = ActiveSheet
    Set DestWs = Worksheets.Add
    
    For Counter = StartCol To StopCol
        Set rng = SourceWs.Cells(StartRow, Counter).Resize(StopRow - StartRow + 1, 1)
        If Application.Min(rng) <> 0 Or Application.Max(rng) <> 0 Then
            DestCol = DestCol + 1
            DestWs.Cells(1, DestCol) = SourceWs.Cells(HeaderRow, Counter)
            rng.Copy DestWs.Cells(3, DestCol)
        End If
    Next
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 

Author Comment

by:Conernesto
Comment Utility
This is perfect. I humbly ask how can I start learning some of this code?

conernesto
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
What do you mean?

:)
0
 

Author Comment

by:Conernesto
Comment Utility
I don't know much of VBA. I would like to start learnig VBA for Excel and Access. For Example, from the code below I don't know what the "i" is or "For i = 3 To Finish Step 3"



Sub CopyX()
Dim i As Long, Start As Long, Finish As Long
Dim rg As Range
Application.ScreenUpdating = False
Start = Columns("F").Column
Finish = Columns("YJ").Column - Start
Set rg = Range("F696:F1356")
For i = 3 To Finish Step 3
    rg.Copy
    rg.Offset(0, i).PasteSpecial xlPasteFormulas
Next
End Sub


conernesto
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Sure.

A For...Next loop allows you to control how many times the code inside the loop executes.  In this case, it is using i as the "counter".

If the first line were:

    For i = 3 To Finish

then i gets initialized to 3, and on each pass through the loop i increments by one.  When the current value of i is equal to Finish, then the code escapes from the loop.

"Step" allows you to increment by a value other than 1.  For example:

    For i = 3 To Finish Step 3

means that when you get to the "Next" statement, it will increment by 3.  And you can go backwards:

    For i = Finish To 3 Step -1

    For i = Finish To 3 Step -3

etc.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Conernesto
Comment Utility
Thank you. Is there a book that you would recommend for reading?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Former Excel MVP John Walkenbach has a very good "Excel <version> Power Programming with VBA" book series, updated for each version.  If you want a slower start, I think Walkenbach also authored the "VBA for Dummies" book.

Bill Jelen, the "MrExcel" behind mrexcel.com, is always a good pick for Excel books: "VBA and Macros: Microsoft Excel 2010"

When you've mastered the basics and want to take it to the next level, go with "Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET" by Rob Bovey et al.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
And of course, you can also hang out here, and see how the Experts answer questions like this.  As soon as you feel up to it, try to answer some questions yourself--I have learned a TON simply from attempting to answer other people's questions.
0
 

Author Comment

by:Conernesto
Comment Utility
One follow-up question. On the explanation above for "i"

If the first line were:

    For i = 3 To Finish

then i gets initialized to 3....  (WHAT IS THE DIFFERENCE ON INITIALIZING TO 3 vs. 4 or 2?)
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
It all depends on what your needs are in the specific situation.  The choice of the bounds for the For...Next are completely arbitrary.

If you happen to know that you have to run an operations against rows 3 to 100, then this makes sense:

    For i = 3 To 100
        Debug.Print Range("a" & i).Row
    Next

makes a lot of sense.  You could also do this:

    For i = 1 To 98
        Debug.Print Range("a" & (i + 2)).Row
    Next

Functional, they are doing the same thing, but IMHO the former approach is probably easier to read and understand than the latter.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now