excel macro for CopyIDtoNewSheet

Dear experts,

I have just started to discover the secrets of excel macro. I tried to record what I would like to do, demonstrating with 4 sets of IDs from one excel book to 4 sheets of another book.

I would like to ask some help to make it work with not fixed number of IDs. That is:

I have a book (CopyIDtoNewSheet) with IDs in column B, with sub IDs in column C, with country name in column F, with date in column J (2008/5/29 format). I get new sets of such data every day and paste it under the previous day's data, separating the "today's" data with an empty row from already received ones.

I have another book called "form" with one sheet in it. I would like to "transport" the latest day data to this file in the way that:

I would like to add as many new sheets to file "form" as many ID I have in book "CopyIDtoNewSheet" in column B, for the latest day (this might be 14, 27, or any, so the number is not fixed, different everyday). Column A has ascending numbers, but under the today's ID data there is nothing in the book (I am not sure this is important info or not...)

Than, I would like to copy paste all IDs, sub IDs, country and date to identical sheets of book "form" and name the sheets with the ID entered to cell K4.

The macro I have recorded demonstrates what I would like to do but to have this result I had to copy each cell content one by one. Can somebody help me to make this work a bit more automatically? BTW, I copy pasted the cell content by selecting the cell with double click because the "form" has some specific format settings...

I hope somebody can help me to fix this matter. Any help, idea is highly appreciated, and many thanks for it in advance.

attis,

[code]
Sub Macro1()
'
' Macro1 Macro
'

'
Workbooks.Open Filename:= _
"C:Documents and SettingsMARIMy Documentsform.xls"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(1)
Sheets("Sheet1").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("B5").Select
ActiveCell.FormulaR1C1 = "3344556677"
Windows("form.xls").Activate
Range("K3").Select
ActiveCell.FormulaR1C1 = "3344556677"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "3344556677"
Range("K3").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("C5").Select
ActiveCell.FormulaR1C1 = "C34567891"
Windows("form.xls").Activate
Range("K4").Select
ActiveCell.FormulaR1C1 = "C34567891"
Windows("CopyIDtoNewSheet.xls").Activate
Range("F5").Select
ActiveCell.FormulaR1C1 = "France"
Windows("form.xls").Activate
Range("I13").Select
ActiveCell.FormulaR1C1 = "France"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J5").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("S2").Select
ActiveCell.FormulaR1C1 = "2008"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J5").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("U2").Select
ActiveCell.FormulaR1C1 = "5"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J5").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("W2").Select
ActiveCell.FormulaR1C1 = "29"
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Copy After:=Sheets(2)
Sheets("Sheet1 (2)").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("B6").Select
ActiveCell.FormulaR1C1 = "4455667788"
Windows("form.xls").Activate
Range("K3").Select
ActiveCell.FormulaR1C1 = "4455667788"
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "4455667788"
Range("K3").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("C6").Select
ActiveCell.FormulaR1C1 = "D45678912"
Windows("form.xls").Activate
Range("K4").Select
ActiveCell.FormulaR1C1 = "D45678912"
Windows("CopyIDtoNewSheet.xls").Activate
Range("F6").Select
ActiveCell.FormulaR1C1 = "England"
Windows("form.xls").Activate
Range("I13").Select
ActiveCell.FormulaR1C1 = "England"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J6").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("S2").Select
ActiveCell.FormulaR1C1 = "2008"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J6").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("U2").Select
ActiveCell.FormulaR1C1 = "5"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J6").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("W2").Select
ActiveCell.FormulaR1C1 = "29"
Sheets("Sheet1 (3)").Select
Sheets("Sheet1 (3)").Copy After:=Sheets(3)
Sheets("Sheet1 (3)").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("B7").Select
ActiveCell.FormulaR1C1 = "5566778899"
Windows("form.xls").Activate
Range("K3").Select
ActiveCell.FormulaR1C1 = "5566778899"
Sheets("Sheet1 (3)").Select
Sheets("Sheet1 (3)").Name = "5566778899"
Range("K3").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("C7").Select
ActiveCell.FormulaR1C1 = "C34567892"
Windows("form.xls").Activate
Range("K4").Select
ActiveCell.FormulaR1C1 = "C34567892"
Windows("CopyIDtoNewSheet.xls").Activate
Range("F7").Select
ActiveCell.FormulaR1C1 = "Italy"
Windows("form.xls").Activate
Range("I13").Select
ActiveCell.FormulaR1C1 = "Italy"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J7").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("S2").Select
ActiveCell.FormulaR1C1 = "2008"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J7").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("U2").Select
ActiveCell.FormulaR1C1 = "5"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J7").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("W2").Select
ActiveCell.FormulaR1C1 = "29"
Sheets("Sheet1 (4)").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("B8").Select
ActiveCell.FormulaR1C1 = "6677890010"
Windows("form.xls").Activate
Range("K3").Select
ActiveCell.FormulaR1C1 = "6677890010"
Sheets("Sheet1 (4)").Select
Sheets("Sheet1 (4)").Name = "6677890010"
Range("K4").Select
Windows("CopyIDtoNewSheet.xls").Activate
Range("C8").Select
ActiveCell.FormulaR1C1 = "D45678913"
Windows("form.xls").Activate
Range("K4").Select
ActiveCell.FormulaR1C1 = "D45678913"
Windows("CopyIDtoNewSheet.xls").Activate
Range("F8").Select
ActiveCell.FormulaR1C1 = "Spain"
Windows("form.xls").Activate
Range("I13").Select
ActiveCell.FormulaR1C1 = "Spain"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J8").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("S2").Select
ActiveCell.FormulaR1C1 = "2008"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J8").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("U2").Select
ActiveCell.FormulaR1C1 = "5"
Windows("CopyIDtoNewSheet.xls").Activate
Range("J8").Select
ActiveCell.FormulaR1C1 = "5/29/2008"
Windows("form.xls").Activate
Range("W2").Select
ActiveCell.FormulaR1C1 = "29"
Range("K3").Select
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and SettingsMARIMy Documents5month29dayform.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWindow.Close
End Sub

[/code]
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories