PDA

View Full Version : Add sheets and copy data



sujittalukde
08-02-2007, 03:51 AM
I am importing data from web and want to save the same in a separate sheet in the same workbook. These are financial data of companies. Cell A1 of "Import" sheet is used to import the data from the web.
I have found another macro which add a sheet with name from a cell but it doesnot copy data.
As cell A1 is used to copy data from web for all the stocks so I need to save these in a separate sheet for further use.

HOw can I do so?

Attaced a sample file with some comments for more clarification.

sujittalukde
08-02-2007, 05:54 AM
I have tried to do the same by recording a macro but the problem is coming with the Cell name and Sheet name ie the macro is recording the sheet name in a fixed manner ie I recorded the macro for Centurion Bank of Punjab but when I changed the cell A1 of Import sheet, it is not adding the data to the new sheet. The recorded macro is


Sub CopyData()
'
' CopyData Macro
' Macro recorded 02/08/2007 by Sujit Talukder
'
'
Application.Run "WebQwerybasic.xls!AddSheetWithNameCheckIfExists"
Sheets("Import").Select
ActiveWindow.SmallScroll Down:=-3
Range("A2:F8,A10:F33").Select
Range("A10").Activate
Selection.Copy
Sheets("Centurion Bank of Punjab").Select
Range("A2").Select
Selection.Columns.AutoFit
Selection.Rows.AutoFit
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Name of the Company"
Range("A1").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Rows("1:1").RowHeight = 20.25
Range("B1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Sheets("Import").Select
ActiveWindow.SmallScroll Down:=-33
Range("A1").Select
Selection.Copy
Sheets("Import").Select
Application.CutCopyMode = False
Sheets("Centurion Bank of Punjab").Select
ActiveCell.FormulaR1C1 = "Centurion Bank of Punjab"
Range("B1:F1").Select
Selection.Font.Bold = True
Sheets("Import").Select
ActiveWindow.SmallScroll Down:=-9
Range("A1").Select
End Sub

sujittalukde
08-02-2007, 09:55 PM
Would someone please help me?