moving data in excel to different files and save with incremental date.
Hi all!
I have included a code which is not currently running because I know I am missing peieces/don't even know if what I have so far is correct.
I have a folder with many files. I want my program to open each and if the first cell in column c is 1 then i want it to
select all the cells until the value in c is 0 and move them to the end of the previous file. Then I want it to go to the next file and do the same etc. The files are arranged by date so
I am struggling trying to figure that part out too. The files names all start with RD followed by the year,month, and date. For example, the file with the name "RD130522" is for the day may 22 2013. So I am trying to assign the characters to variables such as y for year and m for month etc. But I am not sure how to do this (As apparent in my code).
If you have any suggestions please let me know! Much appreciated!
Thanks!
Code:
Sub Splitbook()
Dim strFolder As String, strFile As String
Dim MyData As String, strData() As String
Dim FinalArray() As String
Dim nRow As Long
Dim nStart As Long
Dim nEnd As Long
Dim newend As Long
Dim totalrows As Double
strFolder = "C:\Users\kdunnigan\Documents\Lyo\Sample Log\"
strFile = Dir(strFolder & "*.xlm")
Dim Y As Double 'year
Dim M As Double 'month
Dim D As Double 'day
Y = "13" 'value for first file in sample log folder
M = "04" 'value for first file in sample log folder
D = "24" 'value for first file in sample log folder
Do While strFile <> ""
Open strFolder & strFile For Binary As #1
Dim name As String
name = "RD" & Y & M & D
ActiveWorkbook.SaveAs ("name" & "*.xlm") 'saves file
totalrows = Range("F:F").Count
If Range("C1").Value = 1 Then
For nRow = 1 To totalrows
If Range("D" & nRow).Value = 0 Then
nEnd = nRow - 1
Exit For
End If
Next nRow
If M = 12 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&30"
ElseIf M = 11 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&31"
ElseIf M = 10 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&30"
ElseIf M = 9 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&31"
ElseIf M = 8 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&31"
ElseIf M = 7 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&30"
ElseIf M = 6 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&31"
ElseIf M = 5 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&30"
ElseIf M = 4 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&31"
ElseIf M = 3 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&28"
ElseIf M = 2 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M-1&31"
ElseIf M = 1 And D = 1 Then
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y-1&12&31"
Else
Sheets("Data").Range("A1" & ":L" & nEnd).Move "C:\Users\Sample\RD&Y&M&D-1"
End If
End If
strFile = Dir
Loop
End Sub
Re: moving data in excel to different files and save with incremental date.
So what is step 1? Get a list of all the files in your directory and put them in date order?
Re: moving data in excel to different files and save with incremental date.
Quote:
So what is step 1? Get a list of all the files in your directory and put them in date order?
They are already in date order so for example the first 4 files in the folder might be
RD130510
RD130511
RD130512
RD130513
Re: moving data in excel to different files and save with incremental date.
Quote:
So what is step 1? Get a list of all the files in your directory and put them in date order?
The first step is opening file, if the first value in column c is not 0 then it needs to select all the rows until column c does equal 0 and then move them into the previous days file (after last row) and then save and go to next file and do the same.
Re: moving data in excel to different files and save with incremental date.
"The first step is opening file..."
Which file? RD130510 I assume?
"...select all the rows until column c does equal 0 and then move them into the previous days file (after last row)..."
At that point there IS no previous day's file, is there?
Be as specific as possible, please, so we can help!
