JaseS
asked on
Excel 2007 - need to have data on one sheet copied to another sheet but in a different layout
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
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
Assuming yes, here is my code:
THomas
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
THomas
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Nutch's would work fine if you ran it from the MC tab. I believe mine will work from either tab.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thank you to both! you are fast.
Now the 2nd part of this project I will post as a related question.
ASKER
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!
Summing numbers from multiple sheets?
Related question coming up!
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
Application.ScreenUpdating
'...
Application.ScreenUpdating
This prevents screen updating and speeds up the macro considerably.
Thomas
>Did you see my other project post:
Summing numbers from multiple sheets?
I posted a solution already.
Thomas
Summing numbers from multiple sheets?
I posted a solution already.
Thomas
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 "https://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
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 "https://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
Thanks Kevin, doing fine, and I've cleaned up my profile too.
T
T
should it always be "Y" and "Regular Load"