Solved

Import content from a sheet in workbook to a sheet in another new workbook

Posted on 2011-09-10
16
468 Views
Last Modified: 2012-06-21
For the attached spreadsheet (MC Consolidated Example.xlsm) when button 'Push to Book 1' is clicked it imports the rows from that sheet to another (Book1 Example.xls) WHERE the date in Col J in MC Consolidated equals today's date.

From there I manually copy what is in Book 1 and paste it into another sheet and save that sheet as Mastercard - smTODAY'S DATE.xls. I'd rather just skip the import to Book 1 and go directly to the Mastercard - sm worksheet.

Here's what I need 'Push to Book 1' to do instead:

As you can see, the data imported to 'Mastercard - smSept9-11-Example.xls' from 'MC Consolidated' has changed slightly: there are three zeroes added to the Credit Card number and the date from Col J is not imported, but other than that everything is exactly the same.

What I need now instead is for when button 'Push to Book 1' is clicked:

1. open up another workbook
2. import data from MC Consolidated where the date in Col J is today's date
3. add three zeroes to the front of the card number (and NOT allowing the cell to convert to scientific notation as it wants to do.) -FYI: the credit card numbers do NOT have a dash in them. I inserted in order to change the numbers.
4. make sure the imported to Col E has two decimal places and does not round off
5. populate the top row with the colors and columns headings showing in ''Mastercard - smSept9-11-Example.xls
6. save this now newly created spreadsheet with the date from Col J so that if that date were 9/9/2011 the spreadsheet would save as:  'Mastercard - smSept9-11.xls'

added bonus if possible, (can ask another related question for this part if desired):

7. after doing point 6 above, then open a new email message (outlook 2007)
8. add the attachment of this newly created Mastercard - sm spreadsheet to it
9. add to the body of the email message the below text:

Dear Nalleli,

Please see attached file for regular load request.
PIC:99554Freedom

Please let me know you received this email.

Thank you.

Michael

10. insert into the subject line this text:

MC - smTODAY'S DATE (example: MC-smSept10-11)

 MC-Consolidated-Example.xlsm Book1-Example.xls Mastercard---smSept9-11-Example.xls
0
Comment
Question by:JaseSt
  • 9
  • 7
16 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 36517171
Here it is.
1) open your present workbook and saveas a new name
2) goto Module2 in VBA make sure the lower left icon is clicked so you can view each Sub alone.
3) Delete the present processCC Sub
4) Select ALL in the below code and right click copy and paste in Module2 after any End Sub
5) Save the workbook
6) display Sheet MC Consolidated
7) Right click on the PushToBook1 button and choose Assign Macro.
8) Select the item that end with: Module2.PushToBook and then press OK
9 Save the workbook and exit
10) start the workbook and try PushToBook1 button.
11) after running if it complete successfully it will save the newly created workbook in the same path as the actual file location under the name that you requested.

Pls verify and advise.
gowflow
Sub processCC(sheet As Worksheet)
   Dim length As Integer, I As Integer
   Dim CC, CCFormat As String
   Dim dbl As Double
   Dim LastRow As Long, Row As Long
   
   LastRow = sheet.Cells(Rows.count, "D").End(xlUp).Row
   sheet.Range("D:D").NumberFormat = 0
   sheet.Range("D:D").NumberFormat = "@"
   For Row = 2 To LastRow
      If sheet.Range("D" & Row).Value <> "" Then
         CC = ""
         length = Len(sheet.Range("D" & Row).Value)
         For I = 1 To length
            If Mid(sheet.Range("D" & Row).Value, I, 1) <> " " Then CC = CC & Mid(sheet.Range("D" & Row).Value, I, 1)
         Next
         If Left(CC, 3) <> "000" Then CC = "000" & Format(CC, "0")
         sheet.Range("D" & Row) = CC
      End If
   Next

End Sub





Sub PushToBook()
Dim WS As Worksheet
Dim WSS As Worksheet
Dim NewWS As Worksheet
Dim MaxRow, I, J As Long
Dim NewWb As Workbook
Dim NewWorkB As String

If MsgBox("This process will create a new workbook with today's date and load in it all records in sheet 'MC Consolidated' that beare today's date." & Chr(10) & Chr(10) _
    & "Are you ready to start this process ?", vbQuestion + vbYesNo, "Push To Book") = vbYes Then

    Set WS = ActiveSheet
    Set NewWb = Workbooks.Add
    Set NewWS = NewWb.Sheets("Sheet1")
    NewWS.Name = Format(Now, "mm-dd-yyyy")
    
    'Mastercard - smSept9-11.xls'
    NewWb.SaveAs Filename:=ThisWorkbook.Path & "\Mastercard - sm" & Format(Now, "Mmmd-yy") & ".xls", FileFormat:=xlExcel8
    NewWorkB = NewWb.Name
    
    J = 1
    MaxRow = WS.UsedRange.Rows.count
    WS.UsedRange.AutoFilter Field:=10, Criteria1:=">=" & Date, Operator:=xlAnd, Criteria2:="<=" & Date
    For I = 1 To MaxRow
        If WS.Range(I & ":" & I).EntireRow.Hidden = False Then
            WS.Range("A" & I & ":I" & I).copy NewWS.Cells(J, 1)
            J = J + 1
        End If
    Next I
    WS.ShowAllData
    WS.AutoFilterMode = False
    
    processCC NewWS
       
    With NewWS.Columns("A:I")
       .EntireColumn.AutoFit
       .HorizontalAlignment = xlCenter
    End With
       
    Application.DisplayAlerts = False
    
    For Each WSS In NewWb.Worksheets
        If WSS.Name <> NewWS.Name Then WSS.Delete
    Next WSS
    
    
    NewWb.Close Savechanges:=True
    
    Set NewWb = Nothing
    Set NewWS = Nothing
    Application.DisplayAlerts = True
    
    WS.Activate
    MsgBox ("Workbook: '" & NewWorkB & "' has been created successfully")
    
End If
End Sub

Open in new window

0
 

Author Comment

by:JaseSt
ID: 36517331
Works perfectly, gowflow.

However, I'm not sure I want it to close after saving. I need to look it over to verify everything was imported correctly and that I don't need to edit anything before I send it. So, if you wouldn't mind, please don't have it close after saving.

Also, I'd like it NOT to save in the file you have it saving to. I save this file under a different folder (MasterCardLoadRequests) which can be seen in the attached image.

Any hope of a code opening a new email message and attaching to it as described in points 7-9?  Thank you!

Save Path
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36518232
Well for saving in a location then diffrent story will have to build a routine to ask where to save and save it in registry this is my style in prgramming. I don't like these Fix path to be built into the code I find it cheap and binding to the user !

Will work on this part and give you the details once available. I kep the (send email w attachment part) to an other question as also here it is a whole process that need to be done correctly and also always user set not Developper IMPOSED on user !

gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 36519860
Ok I have completed the choose location for pushtobook however it is a lengthy change in several locations which makes me worried as to your capability to implement the changes. Before going further I wished you had stated this request right from the beginning and not at the end of the change. Anyway.

Pls follow the steps carefully
1) You need to disable Automatic running of macros if this is what you have and set it to manual (Start BlankExcel 2007, click on Office logo on top left corner, clcik on Excel Options, Click on Trust Center, Click on Trust Center Settings, click on Macros Settings and choose the second option Diable all Macros with notification) press OK then OK and Close Excel.) Now everytime you start an Excel file that has macros you will see under the Ribbon a Security Warning telling you that Macros have been disabled. and you hv a button options if you click on it it give you now the choice to enable the macro if you do so only then the macro will get activated. So before doing the rest of the procedure it is important that your macros are disabled or else if you load the attach file you will get zillion errors and will panic !!!

2) Take your last version of the mastercard file and save it in a new name.
3) Save the attached file in the same directory of your latest mastercard file. open this file. You will see 1 sheet called Main. From within this Excel choose open and open the new Mastercard that you just saved in a new name.
4) Select from the ribbon the View menu and click on Arrange ALL and choose Tiled the first option. You will see both files side by side. You have to be very carefule here which one is your Mastercard window and wich one is the file I attached. You will be moving things from the file I attached to the Mastercard file and NEVER the other way around.
5) first thing oyu need to do is DELETE the sheet Main in the Mastercard file. (right click on the sheet - the correct window - and choose delete.)
6) Now you need to copy the Main that you have in the othe rfile to your mastercard file (right click on Main select move or copy, Tick on Create a copy and choose from the top dropdown to Book and choose your mastercard file and click on the first sheet there Final Report. Press OK it should now have copied Main into Mastercard as the first sheet. click on this window and press SAVE.
7) Goto VBA
8) in the left pane you will see both file the looooonnngg one is your mastercard file and the tiny one that have Main and module (module1 and module2) is the file I attached.
9) Doubleclcik on Thisworkbook from your mastercard file and delete all the code that is there
10) doubleclick on Thisworkbook from the file I attached and select all the code right click copy doubleclcik on Thisworkbook from your mastercard file and paste the code there. Press SAVE.
11) doubleclik on Module1 of your mastercard file. click on the icon in the bottom left to view 1 sub at a time you will have the General decalration section
===========================
it starts like this
Global Const APP_CATEGORY = "Software JG"
Global Const APPNAME = "ImportEmailsIntoTabs"
Global wsVisa As Worksheet
Global wsMain As Worksheet
...
and ends like this
Global StartPGM As Boolean
Global CRow As Long
======================
delete the whhole code there. doubleclick on Module1 of the file I attached (do NOT change the bottom icon) and select all the code that is there it should be the declaration section + 2 subs from the menu Edit choose select all Right click COPY and then doubleclick on module1 from your mastercard file and paste the code there. Press on SAVE.
12) doubleclick on module2 of your mastercard file and change the icon in the bottom to left and select the sub PushtoBook and delete the whole sub. gotot module2 from the file I attached and select the whole PushToBook Sub right click copy and come back to module2 of your mastercard file and after any end sub paste the code there.
SAVE the workbook close your mastercard workbook. Close the file attached workbook no need to save it to make sure you preserve its intinal state.
13) Start the neew mastercard wrokbook. You will see the security warning disabled macros click on option and enable macros. The file will start as usual then a window will pop up asking you to choose location for the folder to store PushTobook files. Press on CANCEL now to see what will happen. You will get prompts telling you that the folder is not valid and in the command button in red you will see Browse under Please select a Folder to store PushToBook files. its ok. Goto MC Consolidated and activate pushtoBook button and see what happens. (The routine will not work and you will be asked to select a folder.) Goback to Main and click on browse and select the folder that you want but PLEASE delete from Folder name whatever is written there then press OK and you will see in the commandbutton the loacation that you selected Activated. Now go back to MC consolidated and activate Pushtobook and see what happens. Examine the file (it will remain open) and check the location to make sure it saved it where you want. Save the Excel file and close it. open it again Activate macroes and see what happens. !!!

I hope you will reach shore safely sorry had to put maybe boaring details but past showed me that I need to be very detailed with you or else we may loose precious time and futile mistakes.
gowflow
MasterCard-PushToBook.xlsm
0
 

Author Closing Comment

by:JaseSt
ID: 36529678
Works perfectly, gowflow. Of course I will try it out tomorrow and the next day just to make sure, but I think you got it. Thank you.

Do you want me to give you the other part of this in a related question?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36531069
yes sure pls go ahead, although it is taking time of me but I am getting nicely hooked on your project and keep sending questions I don't have a problem. I feel you will end up with something nice. It seems you forgot your earlier wish to post a question for having fix the workbook. If you do so I do not know how to answer that without posting the workbook.
gowflow
0
 

Author Comment

by:JaseSt
ID: 36531661
0
 

Author Comment

by:JaseSt
ID: 36533705
Put in the code and will try it out on my tomorrow morning.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:JaseSt
ID: 36710605
gowflow, back to this question answered and solved, please note the two attached screenshots. Not sure why MC Consolidated is pulling in another date. It did the same thing yesterday, bringing in a record to MC Consolidated from a date that wasn't today's date. In both cased, the record brought over was the last entry (row) on the page. MC Consolidated AsiaCom Page
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36710832
Is the data coming from an MCR or HMF Account sheet or a regular sheet ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36710849
I would presume it is coming from MCR or HMF Account sheet. PLs confirm
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36710866
I am not getting the trace of this question
0
 

Author Comment

by:JaseSt
ID: 36710987
Data coming from a MCR sheet
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36711011
Don't know why it is doing this but yor post shows Asiacom sheet so its the MCR/HMF routine. Anyway do this fix:

1) Make a new SAVE as your latest mastercard workbook and choose a new name
2) goto vba and doubleclick module1 choose to view 1 sub at a time and display the Sub DailyLoadMCRHMF
3) Replace following line in the code
-------------
If Not Row.Hidden And dataSheet.Cells(Row.Row, cashCol) > 0 And Not Row.Row = 1 Then
-------------
By this following line
-------------
If Not Row.Hidden And dataSheet.Cells(Row.Row, cashCol) > 0 And Not Row.Row = 1 And dataSheet.Cells(Row.Row, dateCol).Value = Date Then
-------------

The best way to do it is to comment out the actual line by puting a single quote in the begining of the line ' once you put it the line turn green. then you paste just under it the new line

4) Save the workbook and exit
5) open it and try it.
gowflow
0
 

Author Comment

by:JaseSt
ID: 36718055
Yep, that worked! You are a genius. Thank you!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36813091
Welcome sorry for not getting it right from the first time !
gowflow
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 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

21 Experts available now in Live!

Get 1:1 Help Now