MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with Cut & Paste

  Asked By: Kiswar    Date: Sep 30    Category: MS Office    Views: 825

I am working on an Excel spreadsheet & I am stuck on a
problem. VBA is new to me but I think it will help me do what I
would like. Here is what I am trying to accomplish. I have a sheet
in a workbook titled Daily Readings in which I can enter numbers to
arrive at the daily totals of various things where I work. There is
an area for each day of the year on this sheet with each day having
45 rows of information. I am trying to copy daily totals onto
another sheet titled Generation which would list the daily total on
a month to month basis. I know I can cut & paste each individual
cell from the Daily Readings sheet to the Generation sheet but it is
so time consuming! What I am looking for is a way to copy every
45th cell in a column from the Daily Readings sheet starting at F9 &
paste them into the Generation sheet in consecutive order in column
C starting at C11. As I said VBA is new to me but with the help of
a few tutorials I have found I think I can figure out where the code
will have to be placed.



2 Answers Found

Answer #1    Answered By: Billie Young     Answered On: Sep 30

Something like this:
Sub blah()
y = 11 'destination row of first result
For x = 9 To 16500 Step 45 'source rows
'the 6 in the line below is the 6th column (F) and the 3 the 3rd(C)
Sheets("Daily Readings").Cells(x, 6).Copy Sheets("Generation").Cells
(y, 3)
y = y + 1
Next x
End Sub

The line beginning 'Sheets' may have been split by yahoo; the line
after it should be 'y = y + 1'

Answer #2    Answered By: Friso Bakker     Answered On: Sep 30

Here is what happened. I opened the
workbook & opened the Microsoft Visual Basic window (Alt + F11). In
the Project – VBAProject window on the left I added a module. In
this Module window, I entered the code as you had written. Under the
Run tab, I selected Run Sub/UserForm. I went back to the Generation
sheet & had entries in the C column but they were not correct.
I do not know if this affects it or not but this is what I think may
have affected the process. In the F column of the daily  Readings
sheet is the following formula =IF(E9=0,0,E9-D9)*100 This takes the
present reading, subtracts the previous reading & multiplies it by a
constant giving me a daily total. If nothing is entered in the E9
cell it returns a 0. After entering the code, cell C11 on the
Generation sheet  show 100. When I click on that cell I get the
following formula, =IF(B11=0,0,B11-A11)*100 It looks to me like it
is taking the day  of the month  from B11 seeing it is not equal to 0,
then taking B11 (First Day Of Month) – A11 (Blank) & multiplying
100. On day # 2 I show 200, each day increases by 100. Is it
because of the formula entered in the Daily Reading sheet I am
trying to copy  from that I end up with this? Any suggestions which
may clear it up?

Didn't find what you were looking for? Find more on Help with Cut & Paste Or get search suggestion and latest updates.