?
Solved

Excel- Copy amounts from one tab to another

Posted on 2013-06-05
10
Medium Priority
?
209 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 93

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 93

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 93

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 93

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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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