Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from one Sheet and Paste to Another
Hello all! I got a little confused by switching back and forth between
pages. As a result, my macro is not doing what I want it to do. My goal is to get the code to start in Cell A3 of a Sheet named €˜Stocks and for each row get the date, then for each column get the stock symbol, then get the stock price; repeat down and over for the entire used range. Below is the code that Im working with: Sub PopulateMacro() 'Delete the sheet "TransposedSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("TransposedSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "TransposedSheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "TransposedSheet" Sheets("Stock").Select Dim ws As Worksheet Dim dtAsOfDate As Date Dim sTicker As String Dim dPrice As Double Set ws1 = Application.ActiveSheet Set ws2 = Application.Sheets("TransposedSheet") Dim LastCol As Long Dim LastRow As Long Dim nWriteRowIndex As Integer nWriteRowIndex = 1 'Assume Row 3 is header LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(3, Columns.Count).End(xlToRight).Column For i = 3 To LastRow dtAsOfDate = ws1.Cells(i, 1) For j = 2 To LastCol sTicker = ws1.Cells(3, j) dPrice = ws2.Cells(i, j) ws2.Cells(nWriteRowIndex, 1) = dtAsOfDate ws2.Cells(nWriteRowIndex, 2) = sTicker ws2.Cells(nWriteRowIndex, 3) = dPrice nWriteRowIndex = nWriteRowIndex + 1 Next Next End Sub This was working fine before, when the data was all on one sheet. Now that Im trying to switch back and forth between a €˜Stock sheet and a €˜TransposedSheet. The results Im getting are all zeros. Thanks for the help! Ryan---. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Paste from Class Sheet to Filtered List on Combined Sheet | Excel Programming | |||
Help to code Macro to Copy fron one sheet and paste in other sheet | Excel Programming | |||
Copy from one Sheet and paste on another sheet based on condition | Excel Discussion (Misc queries) | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
automatic copy and paste from sheet to sheet in a workbook | Excel Programming |