Solved

Excel- Copy amounts from one tab to another

Posted on 2013-06-05
10
202 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
Independent Software Vendors: 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!

 

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

Independent Software Vendors: 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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

738 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