Solved

Excel- Copy amounts from one tab to another

Posted on 2013-06-05
10
180 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
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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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,…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

932 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

17 Experts available now in Live!

Get 1:1 Help Now