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
Solved

Excel 2007 - need to have data on one sheet copied to another sheet but in a different layout

Posted on 2011-03-25
12
255 Views
Last Modified: 2012-05-11
I have a two step project.
First project. Please see attached excel file.

I need the data in the Goga Tora tab to be pasted to the Consolidated tab, IF the date is today's date, and placed under the columns shown in the Consolidated sheet. I have highlighted the customers in different colors to help make it easier to see what I need to have done with a click of a button, and has been done manually.

In the attached example, you will see that in the Consolidated sheet has this done already. I need it done when a button is clicked on the Goga Tora tab. Please feel free to ask if any questions.

The second project will be posted as a related question and assigned points for its completion. Consolidate1.xls
0
Comment
Question by:JaseS
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 7

Expert Comment

by:harr22
ID: 35216800
where are you getting the info for "Activate Y or N" and "Comment"

should it always be "Y" and "Regular Load"
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35216854
Assuming yes, here is my code:

Sub TransferData()
Dim shtConso As Worksheet, shtDet As Worksheet
Dim lgRow As Long, lgCol As Long, i As Long, lgRowDest

Application.ScreenUpdating = False

Set shtConso = ActiveSheet
Set shtDet = Sheets("Goga Tora")

On Error Resume Next
lgRow = Application.Match(CLng(Date), shtDet.Columns("E"), 0)

If Err <> 0 Then
    MsgBox "Date not found"
    Exit Sub
End If

For lgCol = 6 To shtDet.Cells(1, Columns.Count).End(xlToLeft).Column
    If shtDet.Cells(1, lgCol) = "Card Number" Then
        lgRowDest = shtConso.Cells(Rows.Count, "D").End(xlUp).Offset(1).Row
        shtDet.Cells(lgRow, lgCol).Resize(1, 2).Copy shtConso.Cells(lgRowDest, "D")
        shtDet.Cells(lgRow, lgCol).Offset(, 2).Resize(1, 2).Copy shtConso.Cells(lgRowDest, "H")
        shtConso.Cells(lgRowDest, "J") = Date
        shtConso.Cells(lgRowDest, "G") = "Regular Load"
        shtConso.Cells(lgRowDest, "F") = "Y"
    End If
Next

Application.ScreenUpdating = True
End Sub

Open in new window


THomas
0
 
LVL 7

Assisted Solution

by:harr22
harr22 earned 50 total points
ID: 35216940
and assuming yes, here is mine:

 
Sub Foo()
    EndRowGoga = Range("E65536").End(xlUp).Row
    For Each c In Sheets("Goga Tora").Range("E2:E" & EndRowGoga)
        If c.Value = Date Then
            For i = 1 To 28 Step 4
                EndRowMC = Sheets("MC Consolidated").Range("D65536").End(xlUp).Row
                Sheets("Goga Tora").Range(Cells(c.Row, 5 + i).Address _
                    & ":" & Cells(c.Row, 6 + i).Address).Copy
                Sheets("MC Consolidated").Range("D" & EndRowMC + 1).PasteSpecial
                Sheets("MC Consolidated").Range("F" & EndRowMC + 1) = "Y"
                Sheets("MC Consolidated").Range("G" & EndRowMC + 1) = "Regular Load"
                Sheets("Goga Tora").Range(Cells(c.Row, 7 + i).Address _
                    & ":" & Cells(c.Row, 8 + i).Address).Copy
                Sheets("MC Consolidated").Range("H" & EndRowMC + 1).PasteSpecial
                Sheets("MC Consolidated").Range("J" & EndRowMC + 1) = Date
            Next i
        End If
    Next c
End Sub

Open in new window

0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:JaseS
ID: 35216965
Nutch,

Tried your code, thank you, and it copied the right data, but pasted it back on the Goga Tora tab.
Needs to go on the MC Consolidated sheet.

And yes, to har22's question
0
 
LVL 7

Expert Comment

by:harr22
ID: 35216982
Nutch's would work fine if you ran it from the MC tab. I believe mine will work from either tab.
0
 
LVL 39

Accepted Solution

by:
nutsch earned 450 total points
ID: 35217003
My apologies, I had misread from where you wanted to launch the macro:
Sub TransferData()
Dim shtConso As Worksheet, shtDet As Worksheet
Dim lgRow As Long, lgCol As Long, i As Long, lgRowDest

Application.ScreenUpdating = False

Set shtConso = Sheets("MC Consolidated")
Set shtDet = ActiveSheet

On Error Resume Next
lgRow = Application.Match(CLng(Date), shtDet.Columns("E"), 0)

If Err <> 0 Then
    MsgBox "Date not found"
    Exit Sub
End If

For lgCol = 6 To shtDet.Cells(1, Columns.Count).End(xlToLeft).Column
    If shtDet.Cells(1, lgCol) = "Card Number" Then
        lgRowDest = shtConso.Cells(Rows.Count, "D").End(xlUp).Offset(1).Row
        shtDet.Cells(lgRow, lgCol).Resize(1, 2).Copy shtConso.Cells(lgRowDest, "D")
        shtDet.Cells(lgRow, lgCol).Offset(, 2).Resize(1, 2).Copy shtConso.Cells(lgRowDest, "H")
        shtConso.Cells(lgRowDest, "J") = Date
        shtConso.Cells(lgRowDest, "G") = "Regular Load"
        shtConso.Cells(lgRowDest, "F") = "Y"
    End If
Next

Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:JaseS
ID: 35217083
Tried both codes and now they work, however har22 flips screens back and forth, copying and pasting. A bit disconcerting and since my computer tends to crash often, I'd rather not push it.

Thank you to both! you are fast.

Now the 2nd part of this project I will post as a related question.
0
 

Author Closing Comment

by:JaseS
ID: 35217113
Fast expert work by both. Thank you. Saves me a lot of time. Did you see my other project post:
Summing numbers from multiple sheets?

Related question coming up!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35217128
The reason my code doesn't flicker is because of the lines

Application.ScreenUpdating = False
'...
Application.ScreenUpdating = true

This prevents screen updating and speeds up the macro considerably.

Thomas
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35217136
>Did you see my other project post:
Summing numbers from multiple sheets?


I posted a solution already.

Thomas
0
 
LVL 23

Expert Comment

by:redrumkev
ID: 35217583
nutsch,

How are you doing?

FYI - the last 5 links in your profile for questions you have answered in the past, are not correct, they are missing the "www.experts-exchange.com", they just have http:/Q(number)#(post_number).

Nice work on this question - I was trying a solution, but mine was multiple for loops, I like how you merged these, I am going to use this logic elsewhere!

Regards,
Kevin
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35217637
Thanks Kevin, doing fine, and I've cleaned up my profile too.

T
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
stuck in average-1 12 65
Need Help with Mapping and Toggling 3 21
Combine like values in a graph excel 2016 7 30
populate  some cells after data verification 45 19
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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