LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,836
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Paste from Class Sheet to Filtered List on Combined Sheet [email protected] Excel Programming 6 September 16th 08 04:30 PM
Help to code Macro to Copy fron one sheet and paste in other sheet kay Excel Programming 3 July 25th 08 06:46 PM
Copy from one Sheet and paste on another sheet based on condition Prem Excel Discussion (Misc queries) 2 December 24th 07 05:05 AM
Active Cell Copy And Paste Sheet to Sheet A.R.J Allan Jefferys New Users to Excel 4 May 4th 06 02:04 AM
automatic copy and paste from sheet to sheet in a workbook ramseyjramseyj Excel Programming 6 December 11th 04 12:37 AM


All times are GMT +1. The time now is 12:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"