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.
Bookmarks