Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 13th 07, 06:15 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 6
Default VAB to copy cell values into new Sheet, Overwrite if needed and based off of Cell Value in a column

I have two sheets. Sheet1 and Sheet2

On sheet1 I have some finaical numbers that run. For instance I want
to run a particulay day of data and it returns certain values. I would
like to be able to click a command button and have the data copied to
sheet2 into certain cells in a column based of the day of the week.
For instance if B2 on Sheet 1 Equals Monday I want it to find the
column on sheet2 that has Monday in it and copy certain cells from
sheet1 into certain cells in sheet two under that column.

Thanks,

David


  #2   Report Post  
Old July 13th 07, 07:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 6,953
Default VAB to copy cell values into new Sheet, Overwrite if needed and ba

Based strictly on your description, this would be a start.

set rng = Worksheets("Sheet2").Cells.Find( _
worksheets("Sheet1").Range("b2"))
set rng1 = Worksheets("Sheet2").Range("CertainCells").EntireR ow
set rng2 = Intersect(rng1,rng.EntireColumn)
j = 0
for each cell in Worsheets("Sheet1").Range("Data")
j = j + 1
i = 0
for each cell1 in rng2
i = i + 1
if i = j then
cell1 = cell
exit for
end if
Next cell1
Next cell

--
Regards,
Tom Ogilvy


"gumby" wrote:

I have two sheets. Sheet1 and Sheet2

On sheet1 I have some finaical numbers that run. For instance I want
to run a particulay day of data and it returns certain values. I would
like to be able to click a command button and have the data copied to
sheet2 into certain cells in a column based of the day of the week.
For instance if B2 on Sheet 1 Equals Monday I want it to find the
column on sheet2 that has Monday in it and copy certain cells from
sheet1 into certain cells in sheet two under that column.

Thanks,

David


  #3   Report Post  
Old July 13th 07, 07:32 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 110
Default VAB to copy cell values into new Sheet, Overwrite if needed and ba

Debug this. The premise is correct. Just insert a value in B2 and put the
string 'Monday' somewhere in the 10x10 block in sheet 2 and the program will
search for it in sheet 2 and place the B2 contents right below it.

Private Sub CommandButton1_Click()

Dim Monday As Double
Dim i, j As Integer

Monday = Worksheets(1).Cells(2, 1).Value

For i = 1 To 10
For j = 1 To 10
If Worksheets(2).Cells(i, j).Value = "Monday" Then
Worksheets(2).Cells(i, (j + 1)).Value = Monday
End If
Next
Next
End Sub

"gumby" wrote:

I have two sheets. Sheet1 and Sheet2

On sheet1 I have some finaical numbers that run. For instance I want
to run a particulay day of data and it returns certain values. I would
like to be able to click a command button and have the data copied to
sheet2 into certain cells in a column based of the day of the week.
For instance if B2 on Sheet 1 Equals Monday I want it to find the
column on sheet2 that has Monday in it and copy certain cells from
sheet1 into certain cells in sheet two under that column.

Thanks,

David


  #4   Report Post  
Old July 14th 07, 01:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 6
Default VAB to copy cell values into new Sheet, Overwrite if needed and ba

On Jul 13, 1:32 pm, Philosophaie
wrote:
Debug this. The premise is correct. Just insert a value in B2 and put the
string 'Monday' somewhere in the 10x10 block in sheet 2 and the program will
search for it in sheet 2 and place the B2 contents right below it.

Private Sub CommandButton1_Click()

Dim Monday As Double
Dim i, j As Integer

Monday = Worksheets(1).Cells(2, 1).Value

For i = 1 To 10
For j = 1 To 10
If Worksheets(2).Cells(i, j).Value = "Monday" Then
Worksheets(2).Cells(i, (j + 1)).Value = Monday
End If
Next
Next
End Sub



"gumby" wrote:
I have two sheets. Sheet1 and Sheet2


On sheet1 I have some finaical numbers that run. For instance I want
to run a particulay day of data and it returns certain values. I would
like to be able to click a command button and have the data copied to
sheet2 into certain cells in a column based of the day of the week.
For instance if B2 on Sheet 1 Equals Monday I want it to find the
column on sheet2 that has Monday in it and copy certain cells from
sheet1 into certain cells in sheet two under that column.


Thanks,


David- Hide quoted text -


- Show quoted text -


It places 0 to the right of it.

  #5   Report Post  
Old July 14th 07, 01:55 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 6
Default VAB to copy cell values into new Sheet, Overwrite if needed and ba

On Jul 13, 1:06 pm, Tom Ogilvy
wrote:
Based strictly on your description, this would be a start.

set rng = Worksheets("Sheet2").Cells.Find( _
worksheets("Sheet1").Range("b2"))
set rng1 = Worksheets("Sheet2").Range("CertainCells").EntireR ow
set rng2 = Intersect(rng1,rng.EntireColumn)
j = 0
for each cell in Worsheets("Sheet1").Range("Data")
j = j + 1
i = 0
for each cell1 in rng2
i = i + 1
if i = j then
cell1 = cell
exit for
end if
Next cell1
Next cell

--
Regards,
Tom Ogilvy



"gumby" wrote:
I have two sheets. Sheet1 and Sheet2


On sheet1 I have some finaical numbers that run. For instance I want
to run a particulay day of data and it returns certain values. I would
like to be able to click a command button and have the data copied to
sheet2 into certain cells in a column based of the day of the week.
For instance if B2 on Sheet 1 Equals Monday I want it to find the
column on sheet2 that has Monday in it and copy certain cells from
sheet1 into certain cells in sheet two under that column.


Thanks,


David- Hide quoted text -


- Show quoted text -


It appears to run, I get nor errors, but it does not work. The cells
are not copied.

Private Sub CommandButton1_Click()

Set rng = Worksheets("1hr_Interval_Totals").Cells.Find( _
Worksheets("1hr_Intervals").Range("D59"))
Set rng1 = Worksheets("1hr_Interval_Totals").Range("C5:O5").E ntireRow
Set rng2 = Intersect(rng1, rng.EntireColumn)
j = 0
For Each cell In Worksheets("1hr_Intervals").Range("D5258")
j = j + 1
i = 0
For Each cell1 In rng2
i = i + 1
If i = j Then
cell1 = cell
Exit For
End If
Next cell1
Next cell

End Sub



Reply
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 rows from one Data sheet to another sheet based on cell conte John McKeon Excel Discussion (Misc queries) 2 May 15th 10 06:49 AM
Copy last cell with data in column E on one sheet to cell on anoth Seahawk Excel Worksheet Functions 7 May 7th 09 02:52 AM
Copy values from a cell based on values of another cell Spence10169 Excel Discussion (Misc queries) 4 January 13th 09 11:01 AM
based on Cell/Column content ,cut one sheet's values and paste it in other sheet? mindpeace[_3_] Excel Programming 0 May 25th 06 01:33 PM
based on Cell/Column content ,cut one sheet's values and paste it in other sheet? mindpeace Excel Programming 3 May 24th 06 03:33 PM


All times are GMT +1. The time now is 07:43 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017