1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Trouble with Macro to Copy/Paste

Discussion in 'Business Applications' started by JeffSchwartz, Feb 21, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    I have recorded the following macro:

    Sub temp12()
    '
    ' temp12 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+M
    '
    Workbooks.Open Filename:= _
    "C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Sales01.csv"
    Application.Goto Reference:="R1C2:R258C2"
    Selection.Copy
    Windows("MASTER REPORT DATA.xlsx").Activate
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Windows("Sales01.csv").Activate
    ActiveWorkbook.Close (True)
    End Sub


    If I use the assigned shortcut of Ctrl+Shift+M, all it does is the first step of opening "C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Sales01.csv" and nothing more. However, if I run the macro from view macros window it complete the process as desired. Does anyone understand why the shortcut is not working?


    ALSO-- I have a list of 62 "Sales" reports that I would like to have the macro run on. They are Sales01.csv, Sales02.csv,.......Sales31.csv along with Sales01A.csv, Sales02A.csv,........Sales31A.csv. I know that I could just copy and paste those steps into the macro 61 times and change the reference on each pasted copy, but I am wonding if there is a way EITHER to set up some kind of loop to go throught the process 62 times, OR to have it run the process on all files within that "C:\Users\Kurts work c\Desktop\Jeff's stuff\1c" folder?
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    I'm not sure why the short cut isn't working but there are a couple of anomalies that may be worthwhile checking out.

    I'm assuming the Macro is in the MASTER REPORT DATAMASTER REPORT DATA file yet the file extension is .xlsx and it should be .xlsm. See if saving the file as xlsm and changing the macro will fix it.

    I didn't know that you could have a ctrl/alt combination for a macro shortcut. Maybe it something new in 2010 or something I've forgotten about 2003. Perhaps if you re-define the shortcut it will help.

    In the meantime, here is a version of the macro that would loop through your 62 files. If I have some time I'll try to figure a more flexible way to pick up the number of files rather than hard coding the number as I did here.

    I cleaned up some of the inefficiencies of the recorded code and added some flexibility around the number of rows read from the csv files in case that changes in the future. If there's anything you would like explained just ask.


    Code:
    Sub test()
    Application.ScreenUpdating = False
    For i = 1 To 62
            If i > 31 Then
                fName = "Sales0" & i - 31 & "a.csv"
            Else
                fName = "Sales0" & i & ".csv"
            End If
     
            fDir = "C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\"
     
     
            Workbooks.Open Filename:=fDir & fName
     
     
            'Find the last row in col B of the csv file and copy row 1 to last row
            'need this in case number of rows change
                lRow = Range("B" & Rows.Count).End(xlUp).Row
                Range("B1:B" & lRow).Copy
     
     
            Windows("MASTER REPORT DATA.xlsm").Activate
                Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
     
     
     
    ''''''Close the CSV File''''''''''''''''
            Windows(fName).Activate
            Application.DisplayAlerts = False
            ActiveWorkbook.Close (True)
            Application.DisplayAlerts = True
     
     
     Next
     
     
     End Sub
     
  3. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    Thank you. Your coding worked great. I did discover a problem in it (it was looking for Sales010.csv through Sales031.csv instead of Sales10.csv through Sales31.csv) but I was able to fix that. There will always be 62 files, so hardcoding that in is not a problem.

    I did run into the same problem using the shortcut keys but I was able to figure that out and fix it as well.

    Thanks again for helping me with the second to last step of my big project. Now I just need to learn how to work with pivot tables.

    I'm just curious... what do the

    Application.ScreenUpdating = False [and]
    Application.DisplayAlerts = True

    lines do?
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi
    What was the problem with the shortcut?

    The screenupdating set to false freezes the excel screen so that you don't see all the movements from one file to the next and the data being copied and pasted. This cuts the time the macro takes to run dramatically. You would normally set it to true for debugging.

    I first set the display alerts to be false because I was getting the message about a large amount of data in the clipboard when shutting down the csv files. The alerts command suppressed those messages (imagine having to click "no" 61 times). I set it back to True in case some other unanticipated alert comes up that needs attention.

    Good luck with the rest of the project. If you need any help with pivots let me know (also let us know the version of Excel you are using).
     
  5. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    Hi
    For some reason Ctrl+Shift+m did not work correctly. I tried Ctrl+m and that worked fine. It seems that some letters can have the Shift and some cannot.

    By the way, I am using Excel 2010.

    Thanks for explaining those to lines of code. I can definately see how they are valuable.

    Do you know if it is possible to have a single macro open both excel workbooks and word documents or do they have to be written seperately?
     
  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Yes it is possible to open a Word document from an Excel macro but what do you want to do in the Word file?
     
  7. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    I found it easier to tag the text of a .csv file to the end of another .csv file and save it as a new file using the following code (which I am sure could be cleaned up):


    Sub combineONEandTWO()
    '
    ' combineONEandTWO Macro
    ' Ctrl + Shift + O
    '
    ' 01A
    Documents.Open FileName:="C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Z002_01A.CSV", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:="", _
    Encoding:=1252
    Selection.WholeStory
    Selection.Copy
    Documents.Open FileName:="C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Z001_01A.CSV", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:="", _
    Encoding:=1252
    Selection.MoveDown Unit:=wdScreen, Count:=9
    Selection.TypeParagraph
    Selection.PasteAndFormat (wdFormatOriginalFormatting)
    ActiveDocument.SaveAs2 FileName:="C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Sales1A.csv", FileFormat:=wdFormatText, _
    LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
    :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
    SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
    False, Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False _
    , LineEnding:=wdCRLF, CompatibilityMode:=0
    ActiveDocument.Close
    ActiveDocument.Close



    I imagine it is possible to do the same in excel, but I couldn't figure out how.

    By the way, how do you insert code into a box as you did above?
     
  8. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Code:
    sub test()
        dim wb as workbook
        set wb = workbooks.open("C:\abc.csv")
        wb.activesheet.usedrange.offset(1).copy thisworkbook.activesheet.range("A1").end(xldown).offset(1)
    end sub
    
     
  9. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    I don't know if I explained before that the 62 files that I am working with are two files (one from each of two cash registers) for each day of the month. With the set of data from January, I created blank files by hand for the holidays and Sundays that we were not open.

    Is there a way to adapt the If loop code that you gave me above to skip over any files that do not exist?

    If not, would there be a way to have a macro look through a directory and copy and save files for days that do not have them?
     
  10. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Okay - here's version #2

    In this version we are going directly to the directory that contains the CSVs and reading what's there so there is no need to know what files are there - it will read all CSVs including any you may not want. We can refine this if need be.

    If the files are in 2 different directories we can do a loop to cover that - let me know if this is the case and the names of the directories.

    I've also added a Status Bar message showing which csv file is being read. This would be useful if the macro breaks down so that you'll know where it was when it happened. It should flash by pretty quickly but will give you something to look at while it running through 62 files.

    I've also put in a message at the end letting you know how many csv's were read. You can use this as a sanity check but you might want to give some thought to other means of verifying that you've read all the data that is required.

    Here is the updated code (to get the code in a box you need to be in "Advanced" mode, click on the # icon which will put "" in your message. Paste your code between the two "
    Code:
    "s)
     
    Give it a try using a [B]copy of your main workbook.[/B]
     
    [CODE]
    Sub NewTest()
    Application.ScreenUpdating = False
    fDir = "C:\Users\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX  'Specify your CSV directory here
    MyFile = Dir(fDir & "*.csv")
     
            'If MyFile = "" And i = 2 Then GoTo 222     'And Drives(I, 2) = "D"
     
                    'Range("H2").Offset(Count, 0) = MyFile
                    'Range("H2").Offset(Count, 1) = Drives(i, 1)
     
     
            While MyFile <> ""
     
            Application.StatusBar = "CSV File > " & MyFile
     
            Workbooks.Open Filename:=fDir & MyFile
     
            'Find the last row in col B of the csv file and copy row 1 to last row
            'need this in case number of rows change
                lRow = Range("B" & Rows.Count).End(xlUp).Row
                Range("B1:B" & lRow).Copy
     
            Windows("MASTER REPORT DATA.xlsm").Activate
                Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
     
     
    ''''''Close the CSV File''''''''''''''''
            Windows(MyFile).Activate
            Application.DisplayAlerts = False
            ActiveWorkbook.Close (True)
            Application.DisplayAlerts = True
     
                MyFile = Dir
                        Count = Count + 1
            Wend
     
            Application.StatusBar = False
            MsgBox (Count & " csv's read successfully")
    End Sub
    
     
  11. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    Hi,

    Thanks again for all your help.

    I'm gonna try to figure this out myself, but I just realized that I am going to need to specify which worksheet within the workbook [MASTER REPORT DATA.xlsm] to have that code save to. I intend to eventually have this worksheet for SALES data, another worksheet for DEPARTMENT data, another worksheet for TIME OF DAY data, and a fourth for CLERK data. I will be able to build each of those the same way you have helped me to build the SALES data worksheet.

    I have enclosed the code for a macro I created to prepare the [SALES] files that we have been working with. If you would like to, I would find it interesting to know how I could streamline that code. For instance, is there a better way that to repeat the line [ Selection.EntireRow.DELETE ] five times?


    Code:
    Sub ONE()
    '
    ' ONE Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
        Range("A9:A120").Select
        Selection.DELETE Shift:=xlToLeft
        Dim xRow As Long
        For xRow = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        Range("A" & xRow).Select
        Selection.EntireRow.Insert
        Selection.Value = Trim(Selection.Offset(1, 0).Value) & " #"
        Selection.Offset(0, 1).Value = Selection.Offset(1, 1).Value
        Selection.Offset(0, 2).Clear
        Selection.Offset(1, 0).Value = Trim(Selection.Offset(1, 0).Value) & " $"
        Selection.Offset(1, 1).Value = Selection.Offset(1, 2).Value
        Selection.Offset(1, 2).Clear
    Next xRow
        Application.Goto Reference:="R16C1"
        Selection.EntireRow.DELETE
        Selection.EntireRow.DELETE
        Selection.EntireRow.DELETE
        Selection.EntireRow.DELETE
        Selection.EntireRow.DELETE
        Application.Goto Reference:="R14C1"
        Selection.EntireRow.DELETE
        Application.Goto Reference:="R12C1"
        Selection.EntireRow.DELETE
        Application.Goto Reference:="R10C1"
        Selection.EntireRow.DELETE
        Application.Goto Reference:="R8C1"
        Selection.EntireRow.DELETE
        Application.Goto Reference:="R6C1"
        Selection.EntireRow.DELETE
        Application.Goto Reference:="R4C1"
        Selection.EntireRow.DELETE
        Application.Goto Reference:="R2C1"
        Selection.EntireRow.DELETE
        Range("B8").Select
        Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
        ActiveWorkbook.Close (True)
    End Sub

    So far I have just opened all of the files that I wanted to run this process on and hit the command prompt that many times. I assume I can use the code you just sent me ( changing the center part) to have it run this process on all the files within a directory.

    I am slowly learning this stuff. Thanks for the tutoring!
     
  12. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi Jeff

    I take it that this last macro is just an experiment in need of more analysis. I say this because there are a few seeming anomalies in the code (I say "seeming" because I don't know what your CSVs contain and why you are altering them so drastically).

    For example, where you have "Selection.Offset(0, 2).Clear" that is clearing an empty cell because it is in a new row that you just created

    Where you delete a series of rows (which can be done in a single line like this

    "Range("A16:A21").EntireRow.Delete"

    you are deleting rows you have just added and altered.

    In the original macro we were reading in only Column B of the CSV. All the new data you just generated would not be included.

    However, assuming there are valid reasons for doing what you are doing the one major concern I would have is that you are altering data sourced from other applications. This should normally be avoided at all costs because you would be eliminating the audit trail for verifying if your final output is correct.

    Imagine if someone questions your numbers from your Master spread sheet and you need to prove that they are correct. You would go back to the source files and show where the data comes from. But you have changed the source and possibly (probably) deleted the data that is being questioned.

    I would strongly consider the option of reading all the source data as is and doing the manipulations in the Master sheet. If anything goes wrong there you can regenerate the Master data easily enough because the source data is still intact.

    In addition, I would create references to the CSV file names in the Master sheet as you read them in - e.g. create a column in the master sheet where the name and date of the CSV being read is recorded. This will make debugging much easier.

    Once you have all the data in one place and auditable then I would start the manipulations to get you the information you need.
     
  13. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    The following the file created by the cash register. The "FILE001" and the date tells us what orignal file it comes from.

    ...........................

    The code that I called "ONE" deletes the RECORD number, and creates two lines for each of the DESCRIPTORS- one with a "#" for the QUANTITY/No and one with a "$" for the AMOUNT. Then all the data is put into column B. The MASTER REPORT DATA has [DESCRIPTOR]#,[DESCRIPTOR]$,[DESCRIPTOR]#,[DESCRIPTOR#,....as column headings and the code that you helped me with takes the column B of data and places it in the MASTER REPORT DATA file as a row.

    We have two such reports for each day, one from each cash register.

    I didn't know how to get the process for spliting each row into two rows once it gets up to the TIME, DATE, COUNTER,.... that is why I am deleting rows that I had just created.

    This is the way that I came up with for getting everything in one place. I imagine there is a better way to do that and I am willing to learn how.

    I copy the original data files from their original source, so that information is not lost.

    So you can know... I also have a macro TWO that does this same process with a second set of files which are really just SALES data part two. Once I have the 01 files and 02 files narrowed down to two column I used another code to merge parts one and two together.

    This is all somewhat confusing, I've just tried until I could find a process that would work. Through all of it I have learned a great deal about how to write/record VB code.
     
  14. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    How do I insert a worksheet?? I think I was able to attach the worksheet I intended to enclose in the last message here.
     

    Attached Files:

  15. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Okay

    I'll take your word for it. Here is your code streamlined somewhat

    Code:
    [SIZE=1]Sub ONE2()[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]'[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]' ONE Macro[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]'[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]' Keyboard Shortcut: Ctrl+q[/SIZE]
    [SIZE=1][/SIZE][SIZE=1] [/SIZE]
    [SIZE=1][/SIZE][SIZE=1] [/SIZE]
    [SIZE=1][/SIZE][SIZE=1]Dim xRow As Long[/SIZE]
    [SIZE=1][/SIZE][SIZE=1] [/SIZE]
    [SIZE=1][/SIZE][SIZE=1]   lRow = Range("A" & Rows.Count).End(xlUp).Row[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]       Range("A9:A" & lRow).Delete Shift:=xlToLeft[/SIZE]
    [SIZE=1][/SIZE][SIZE=1] [/SIZE]
    [SIZE=1][/SIZE][SIZE=1]For xRow = lRow To 11 Step -1[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Range("A" & xRow).Select[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Selection.EntireRow.Insert[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Selection.Value = Trim(Selection.Offset(1, 0).Value) & " #"[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Selection.Offset(0, 1).Value = Selection.Offset(1, 1).Value[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Selection.Offset(1, 0).Value = Trim(Selection.Offset(1, 0).Value) & " $"[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Selection.Offset(1, 1).Value = Selection.Offset(1, 2).Value[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Selection.Offset(1, 2).Clear[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]Next xRow[/SIZE]
    [SIZE=1][/SIZE][SIZE=1] [/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Range("A9:A10").EntireRow.Delete              'gets rid of the header rows[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    Range("B8").NumberFormat = "[$-409]h:mm AM/PM;@"[/SIZE]
    [SIZE=1][/SIZE][SIZE=1] [/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    ActiveWorkbook.Close (True) [/SIZE]
    [SIZE=1][/SIZE][SIZE=1] [/SIZE]
    [SIZE=1][/SIZE][SIZE=1]End Sub[/SIZE]
    Here is a pretty good guide on how to debug your code. It's usually revealing when you watch your code perform one line at a time and when you discover inefficiencies n the code that can be streamlined. Give it a read and try to utilise some of its recommendations.

    http://www.computing.net/howtos/show/debugging-vba-code-101-a-tutorial/720.html
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090496

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice