Copy and Paste VBA

Learning Excel

Board Regular
Joined
Jul 27, 2002
Messages
99
Hi,
I have a datasheet with 5000 rows.
There is week number in column E, starting from 1 to 52 in different rows
What I want is to populate the week in this column to Column A and populate till the next week number is found.
This will allow me to do sorting etc.
Appreciate some guidance on this.

Before ( Date in Column B through D are not the same, for illustration only)

A B C D E

Blank X Y Z 1
Blank X Y Z
Blank X Y Z
Blank X Y Z 2
Blank X Y Z
Blank X Y Z 3
Blank X Y Z

What I want

A B C D E

1 X Y Z 1
1 X Y Z
1 X Y Z
2 X Y Z 2
2 X Y Z
3 X Y Z 3
3 X Y Z

Attempt to write a macro, but is not what I want.
Sub AddWeek()


FinalRow = Cells(5000, 5).End(xlUp).Row
For i = FinalRow To 7 Step -1
If Cells(i, 5).Value >= "1" Then
Cells(i, 5).Copy Destination:=Cells(i, 1)
End If
Next i

End Sub

Thank
Learning Excel
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
Sub Test()
    Dim Rng As Range
    Set Rng = Range("A2:A" & Range("B65536").End(xlUp).Row)
    With Rng
        .FormulaR1C1 = "=MAX(R2C[4]:RC[4])"
        .Value = .Value
    End With
End Sub

The code assumes your data starts in row 2 and that you want to fill to the last used row in column B. Amend if necessary.
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
No need for a macro.

Copy Col E and paste into Col A. Select all Col A and do edit / Go To / Special / Blanks, then type = and hit the Up Arrow once and use CTRL+ENTER to enter the data.

If you want to get rid of the formulas just copy Col A and paste special as values.


Addendum

Your data looks ripe for a Pivot table, and Pivot Table functionality will take you only a few minutes to set up. Depending on what analysis you need to do it could be the best few minutes you ever spent (Fantastic thing to learn no matter what though, and yet so simple).

If you were interested then check out the following links that will give you a step by step guied on how to do this as well as examples of what you can get out:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm
 

Learning Excel

Board Regular
Joined
Jul 27, 2002
Messages
99
Hi,
Thank you for looking into my problem.
I believe the sample that I have provided is distorting, it should be :-

A B C D E

Blank X Y Z 1
Blank X Y Z Apple
Blank X Y Z Orange
Blank X Y Z 2
Blank X Y Z Grape
Blank X Y Z 3
Blank X Y Z Lemon

What I want

A B C D E

1 X Y Z 1
1 X Y Z
1 X Y Z
2 X Y Z 2
2 X Y Z
3 X Y Z 3
3 X Y Z

There are data in between the week(Column E) that I do not want to remove.
There are actually more Columns which is up to Column Z instead of E as an example.

Your proposal have given me an idea. That is Column E to Column A
Remove anything that is not number than do a F5 fill blank cell.
This may not be effective but should do the work.


Learning Excel
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top