Solved

Excel- Copy amounts from one tab to another

Posted on 2013-06-05
10
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39223902
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
ID: 39224071
This is perfect. I humbly ask how can I start learning some of this code?

conernesto
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39229719
What do you mean?

:)
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Conernesto
ID: 39229924
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
ID: 39229949
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
 

Author Comment

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

Expert Comment

by:Patrick Matthews
ID: 39230063
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
ID: 39230068
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
ID: 39230474
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
ID: 39230514
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

687 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