+ Reply to Thread
Results 1 to 2 of 2

Copy from one workbook to another and insert current date and time in cells. Unique Code!

Hybrid View

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    stl, mo
    MS-Off Ver
    Excel 2007
    Posts
    1

    Copy from one workbook to another and insert current date and time in cells. Unique Code!

    Looking to copy a row from one workbook(wbk1) that is already open to another workbook(wbk2)not open. the date from wbk 1 needs to be appended to the data in wbk2. I also want to place a current date in a cell and time in another cell for each time I do the copy. The date and time cells are pasted in wbk2. Finally the wbk2 is closed and saved with the current date as the name of the file.

    The ultimate plan is to place this code in a ONTIME function where it loops until a set time, but I'll handle that later.

    Any help is very much appreciated. Love this forum btw!
    Code is below:

    
    
    
    
    Sub TheSub()
    
    Dim wbk As Workbook
    Dim varCellvalue As String
    Dim strFirstFile As String
    Dim strSecondFile As String
    
    
    varCellvalue = Cells(1, 1).Value
    varCellvalue1 = Format(varCellvalue, "YYYY_MM_DD")
    varCellvalue2 = Format(varCellvalue, "YYYY-MM-DD HH:MM:SS")
    
    strFirstFile = "C:\Users\JElgin.000\Desktop\Book3.csv"
    strSecondFile = "C:\Users\JElgin.000\Desktop\Book1.xlsx"
    
    Set wbk1 = Workbooks.Open(strFirstFile)
    With wbk1.Sheets("Book3")
        .Range("A1:L1").Copy
    End With
    
    
    Application.DisplayAlerts = False
    
    Set wbk2 = Workbooks.Open(strSecondFile)
    With wbk2.Sheets("Sheet1")
        .Cells(lRow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    End With
    
    Dim dtmDate As Date
    
    dtmDate = DateValue(Now)
    dtmDate = .Cells(lRow, 2).Value
    
    
    Dim dtmTime As Date
    
    dtmTime = TimeValue(Now)
    dtmTime = .Cells(lRow, 3).Value
    
    
    Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
    
        'Copy the sheet to a new workbook
        ActiveSheet.Copy
        Set Destwb = ActiveWorkbook
    
    TempFilePath = "C:\Users\JElgin.000\Desktop\""
        
        TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "yyyy-mm-dd hh-mm-ss")
    
        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            .Close savechanges:=True
        End With
    
    wbk2.Close strSecondFile
    Application.DisplayAlerts = True
    
    End Sub

    Moderator's Note: Use code tags on your codes, since this is your first post I'll do it for you. Thanks.
    Last edited by vlady; 10-17-2012 at 02:17 AM. Reason: Please use proper code tags.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Copy from one workbook to another and insert current date and time in cells. Unique Co

    Sub TheSub()
    varCellvalue2 = Format(Now(), "YYYY-MM-DD HH:MM:SS")
    strFirstFile = "C:\Users\JElgin.000\Desktop\Book3.csv"
    strSecondFile = "C:\Users\JElgin.000\Desktop\Book1.xlsx"
    Set wbk1 = Workbooks.Open(strFirstFile)
    With wbk1.Sheets(1)
    .Range("A1:L1").Copy
    End With
    Set wbk2 = Workbooks.Open(strSecondFile)
    With wbk2.Sheets(1)
      lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
      .Cells(lRow, 1).PasteSpecial
      .Cells(lRow + 1, 2).Value = varCellvalue2
    End With
    End Sub
    the csv file must be with comma delimiters
    If solved remember to mark Thread as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1