rrosa1
04-24-2010, 02:46 PM
hi
I need real help as some type of VBA code for this problem.
I need to create an automated process to copy data from book1 with 8 or 9 sheet in it and book2 with 5 to 6 sheet and paste it in another workbook called destination.xls. with 2 sheet for each Book. I have vba code thanks to this forum and copy paste it work.
but i need to do some modification in to this code to do this task whenever I click in the destination.xls sheet
right now it dose copy the row from sheet1 since there is more sheet in book 1 is there any way it could check the book1 for all sheet in book and copy the range A12:G12 only
and paste to destination.xls Book sheet1 like,
in destination.xls Book sheet1
data from book1 sheet1 row A12:G12
data from book1 sheet2 row A12:G12
data from book1 sheet3 row A12:G12
data from book1 sheet4 row A12:G12
.
.
.
onward till last sheet in book1
than
in destination.xls Book sheet2
data from book2 sheet1 row A12:G12
data from book2 sheet2 row A12:G12
data from book2 sheet3 row A12:G12
data from book2 sheet4 row A12:G12
.
.
.
onward till last sheet in book2
here is my code:
Sub Rectangle1_Click()
Dim i As Integer
'Open up your first workbook, go to Sheet1, and copy rows 6-12
Workbooks.Open "C:\Documents and Settings\NoShow\book1.xls"
Sheets("Sheet1").Activate
Sheets("Sheet1").Rows("12:12").Copy 'here i wnat to copy only A12:F12
'not the hole Rows
'next line code replacing the data from previous line copy i want to add to 'the next line
'Sheets("Sheet2").Rows("12:12").Copy
'Go back to third workbook, go to sheet 1, and insert the copied rows
Workbooks("destination.xls").Activate
Sheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Insert shift:=xlShiftDown 'Insert next to the data 'available row not up side of the data
'Go back to first workbook and close
Workbooks("book1.xls").Activate
Workbooks("book1.xls").Close savechanges:=False
'Open up your second workbook, go to Sheet1, and copy rows 1-15
Workbooks.Open "C:\Documents and Settings\NoShow\book2.xls"
Sheets("Sheet1").Activate
Sheets("Sheet1").Rows("15:15").Copy
'Go back to third workbook, go to sheet2, and insert the copied rows.
Workbooks("destination.xls").Activate
Sheets("Sheet2").Activate
ActiveSheet.Cells(2, 1).Insert shift:=xlShiftDown 'Insert next to the data
'avilable row not up side of the data
'Delete rows 6-9, since you did not want them included
'For i = 1 To 4
' ActiveSheet.Rows(6).Delete
'Next i
'Go back to your second workbook and close
Workbooks("book2.xls").Activate
Workbooks("book2.xls").Close savechanges:=False
'Now your third workbook should be open with the data pasted in.
End Sub
Is my explanation clear enough?
Please
Any help would be highly appreciated.
Thanks in advance!
I need real help as some type of VBA code for this problem.
I need to create an automated process to copy data from book1 with 8 or 9 sheet in it and book2 with 5 to 6 sheet and paste it in another workbook called destination.xls. with 2 sheet for each Book. I have vba code thanks to this forum and copy paste it work.
but i need to do some modification in to this code to do this task whenever I click in the destination.xls sheet
right now it dose copy the row from sheet1 since there is more sheet in book 1 is there any way it could check the book1 for all sheet in book and copy the range A12:G12 only
and paste to destination.xls Book sheet1 like,
in destination.xls Book sheet1
data from book1 sheet1 row A12:G12
data from book1 sheet2 row A12:G12
data from book1 sheet3 row A12:G12
data from book1 sheet4 row A12:G12
.
.
.
onward till last sheet in book1
than
in destination.xls Book sheet2
data from book2 sheet1 row A12:G12
data from book2 sheet2 row A12:G12
data from book2 sheet3 row A12:G12
data from book2 sheet4 row A12:G12
.
.
.
onward till last sheet in book2
here is my code:
Sub Rectangle1_Click()
Dim i As Integer
'Open up your first workbook, go to Sheet1, and copy rows 6-12
Workbooks.Open "C:\Documents and Settings\NoShow\book1.xls"
Sheets("Sheet1").Activate
Sheets("Sheet1").Rows("12:12").Copy 'here i wnat to copy only A12:F12
'not the hole Rows
'next line code replacing the data from previous line copy i want to add to 'the next line
'Sheets("Sheet2").Rows("12:12").Copy
'Go back to third workbook, go to sheet 1, and insert the copied rows
Workbooks("destination.xls").Activate
Sheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Insert shift:=xlShiftDown 'Insert next to the data 'available row not up side of the data
'Go back to first workbook and close
Workbooks("book1.xls").Activate
Workbooks("book1.xls").Close savechanges:=False
'Open up your second workbook, go to Sheet1, and copy rows 1-15
Workbooks.Open "C:\Documents and Settings\NoShow\book2.xls"
Sheets("Sheet1").Activate
Sheets("Sheet1").Rows("15:15").Copy
'Go back to third workbook, go to sheet2, and insert the copied rows.
Workbooks("destination.xls").Activate
Sheets("Sheet2").Activate
ActiveSheet.Cells(2, 1).Insert shift:=xlShiftDown 'Insert next to the data
'avilable row not up side of the data
'Delete rows 6-9, since you did not want them included
'For i = 1 To 4
' ActiveSheet.Rows(6).Delete
'Next i
'Go back to your second workbook and close
Workbooks("book2.xls").Activate
Workbooks("book2.xls").Close savechanges:=False
'Now your third workbook should be open with the data pasted in.
End Sub
Is my explanation clear enough?
Please
Any help would be highly appreciated.
Thanks in advance!