Find copy and create new excel files w macro

Closed
hero85 Posts 7 Registration date Thursday March 12, 2009 Status Member Last seen May 14, 2009 - Mar 13, 2009 at 09:33 AM
reddishpink Posts 2 Registration date Saturday November 7, 2009 Status Member Last seen November 8, 2009 - Nov 8, 2009 at 05:28 PM
Hello,
I would like to create a macro to save time. I receive data which I then have to process in excel.
Right now, I need help with copying and paste the data into new excel files automatically.
The data I receive is always like this:
--Start test--
45
34
23
454
232
--End Test--
--Start test--
99
33
22
--End Test--

So in this example, I received a file which had two tests in it that means two different files should be created. So I need to copy everything in between start test and end test. And then paste it into a new excel file.
Any links or any type of help will be appreciated.

2 replies

hero85 Posts 7 Registration date Thursday March 12, 2009 Status Member Last seen May 14, 2009 8
Mar 17, 2009 at 05:22 PM
Ok, I will try this code soon. I hope it works. Also is there not a way to copy from one file to another? I don't want to transfer to a sheet but to a file!
8
This code assumes that the data is in column A and there are no empty rows from the start to the end of the data. Also, the data is copied from Sheet2 to Sheet1 and the rows to copy start in row one and will be copied to row one.


Sub CopyToNewSheet()

Set o = Sheets("Sheet1") 'Where the data is copied to. You can rename it to your sheet name.
Set t = Sheets("Sheet2") 'Where the data resides. You can rename it to your sheet name.
Dim CpyData
Dim d
CpyData = 0
d = 1


Do While Not IsEmpty(t.Range("A" & d))

If IsNumeric(t.Range("A" & d)) Then
CpyData = CpyData + 1

o.Range("A" & CpyData) = t.Range("A" & d)

End If

d = d + 1

Loop

End Sub
3
reddishpink Posts 2 Registration date Saturday November 7, 2009 Status Member Last seen November 8, 2009
Nov 8, 2009 at 05:28 PM
hello ive a similar problem here.. im a newbie to VBA Script so i kinda dont have much clue of what should i do and stuff.
my case is a bit similar.. what happens when say i have 365 files (one file per day, through out the whole yr).. each file when opened in excel has 23 columns.. the only columns that i want to use is column A(minute) and column X(Solar Radiation)..

How do i import these 2 columns from each files , and have them in just one sheet, so that i only have :

in column A - minute
column B to colum ZZ - day 1 to day 365

and using macros.

Thanks!!
0