Microsoft Excel - Excel/VBA Code - Asked By Aashish Bhangdia on 14-Aug-08 01:36 AM

I have created a master sheet in excel and want to capture the data from it onto the next sheet, in a row. can anybody help me with the macro code..?

Extracting data from a master worksheet to various work sheets - Binny ch replied to Aashish Bhangdia on 14-Aug-08 01:49 AM

Execute CopySheetToSheet() ...

Sub CopySheetToSheet()
Sheets("Sheet1").Select

StartRow = 1

For Each Cell In Range(Cells(3, 1), Cells(ActiveSheet.UsedRang

e.Cells.SpecialCells(xlLastCell).Row, 1))
Call CopyCellToCell(Cell.Row, Cell.Column, StartRow, 1)
Call CopyRowToColumn(StartRow, 2, 2)
Call CopyRowToColumn(StartRow, 1, 3)
StartRow = CopyRowToColumn(StartRow, Cell.Row, 4)
Next
End Sub

Function CopyRowToColumn(ByVal StartRow, ByVal RowNumber, ByVal ColumnNumber)
Sheets("Sheet1").Select
LastColumn = ActiveSheet.UsedRange.Cells.SpecialCells(xlLastCell).Column

For Each Cell In Range(Cells(RowNumber, 2), Cells(RowNumber, LastColumn))
Call CopyCellToCell(Cell.Row, Cell.Column, StartRow, ColumnNumber)

StartRow = StartRow + 1
Next

CopyRowToColumn = StartRow
End Function

Sub CopyCellToCell(ByVal FromRow, ByVal FromColumn, ByVal ToRow, ByVal ToColumn)
Sheets("Sheet1").Select
Cells(FromRow, FromColumn).Select
Selection.Copy

Sheets("Sheet2").Select
Cells(ToRow, ToColumn).Select
ActiveSheet.Paste
End Sub
hope these example will help you:
Go through these links:
http://en.allexperts.com/q/Excel-1059/Extracting-data-master-worksheet.htm
http://www.experts-exchange.com/Programming/Languages/Q_22136694.html

this is vb code:

FName = thisworkbook.range("A1")
set MasterBk = workbooks.open(FName)
FName = thisworkbook.range("A2")
set CopyBk = workbooks.open(FName)

for each sht in MasterBk.sheets
sht.cells.copy _
destination:=Copybk.sheets(sht.name).cells
next sht
CopyBK.close savechanges:=true
this will help you:
http://www.eggheadcafe.com/software/aspnet/32639062/how-to-transfer-data-from.aspx
MasterBK.close savechanges:=False

Try this - ram kumar replied to Aashish Bhangdia on 14-Aug-08 01:53 AM

Hi,

Imports Office = Microsoft.Office.Core

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
      System.EventArgs) Handles Button1.Click
   Dim oExcel As Excel.Application
   Dim oBook As Excel.Workbook
   Dim oModule As VBIDE.VBComponent
   Dim oCommandBar As Office.CommandBar
   Dim oCommandBarButton As Office.CommandBarControl
   Dim sCode As String

' Create an instance of Excel, and show it to the user.
oExcel = New Excel.Application()

' Add a workbook.
oBook = oExcel.Workbooks.Add

' Create a new VBA code module.
oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule)

   sCode = "sub VBAMacro()" & vbCr & _
      "   msgbox ""VBA Macro called"" " & vbCr & _
      "end sub"

' Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(sCode)

   Try
      ' Create a new toolbar, and show it to the user.
      oCommandBar = oExcel.CommandBars.Add("VBAMacroCommandBar")
      oCommandBar.Visible = True

      ' Create a new button on the toolbar.
      oCommandBarButton = oCommandBar.Controls.Add(Office.MsoControlType.msoControlButton)
      ' Assign a macro to the button.
      oCommandBarButton.OnAction = "VBAMacro"
      ' Set the caption of the button.
      oCommandBarButton.Caption = "Call VBAMacro"
      ' Set the icon on the button to a picture.
      oCommandBarButton.FaceId = 2151
   Catch exc As Exception
         MessageBox.Show("VBAMacroCommandBar already exists.", "Error")
   End Try

   oExcel.Visible = True
   ' Set the UserControl property so that Excel does not shut down.
   oExcel.UserControl = True

   ' Release the variables.
   oCommandBarButton = Nothing
   oCommandBar = Nothing
   oModule = Nothing
   oBook = Nothing
   oExcel = Nothing

' Force garbage collection.
GC.Collect()

End Sub

Add the following code to the top of Form1.vb:Imports Office = Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports VBIDE = Microsoft.Vbe.Interop

Excel / VBA Code - Aashish Bhangdia replied to Binny ch on 14-Aug-08 02:20 AM

Thanks for replying so fast, but i guess this is a generic and I am not that good in VB coding. If you want i can send you the sheet and you can have clear idea what i want...