I want to write a macro to copy down €œHoliday€� in column A until the row
where €œVacation€� appears. And then copy down €œVacation€� in the subsequent
rows. And the in column B, I want to copy down €œTeam A€� until the next team
is mentioned. However, I dont want the macro applied to the columns where I
have the # of hours listed.

Suggestions?

Type Team Name Jan Feb Mar Apr
Holiday Team A Tom 16 8
Dick 16 8
Harry 16 8 12
Team B Sue 16
Kim 16 8
Sam 16 8
Team C George 16 8
John 16 8
Jim 8
Vacation Team A Tom 8
Dick 8
Harry
Team B Sue 8
Kim
Sam 8
Team C George 8
John 8
Jim

Enter the formula in say J2
=J1

Select column A.
Press F5. Click Special
From GoTo window select Blanks.
Right clickPaste SpecialFormulas..

If this post helps click Yes
---------------
Jacob Skaria


"Dave Shultz" wrote:

I want to write a macro to copy down €œHoliday€� in column A until the row
where €œVacation€� appears. And then copy down €œVacation€� in the subsequent
rows. And the in column B, I want to copy down €œTeam A€� until the next team
is mentioned. However, I dont want the macro applied to the columns where I
have the # of hours listed.

Suggestions?

Type Team Name Jan Feb Mar Apr
Holiday Team A Tom 16 8
Dick 16 8
Harry 16 8 12
Team B Sue 16
Kim 16 8
Sam 16 8
Team C George 16 8
John 16 8
Jim 8
Vacation Team A Tom 8
Dick 8
Harry
Team B Sue 8
Kim
Sam 8
Team C George 8
John 8
Jim

Hi, you don't need a macro to do that, just follow these instructions:
Select de Range with the Data (CTRL+A), then Edit-Go to (CTRL+G), then
Special-Blanks and click OK. Now press F2 and write =CR where CR- the column
and row from which you want to replicate (copy) the data, for instance =A1,
and press CTRL+Enter and that's it, your data will be copied as you wanted.
--
Alex
*Remember to click "yes" if this post helped you. Thank you!



"Dave Shultz" wrote:

I want to write a macro to copy down €œHoliday€� in column A until the row
where €œVacation€� appears. And then copy down €œVacation€� in the subsequent
rows. And the in column B, I want to copy down €œTeam A€� until the next team
is mentioned. However, I dont want the macro applied to the columns where I
have the # of hours listed.

Suggestions?

Type Team Name Jan Feb Mar Apr
Holiday Team A Tom 16 8
Dick 16 8
Harry 16 8 12
Team B Sue 16
Kim 16 8
Sam 16 8
Team C George 16 8
John 16 8
Jim 8
Vacation Team A Tom 8
Dick 8
Harry
Team B Sue 8
Kim
Sam 8
Team C George 8
John 8
Jim

Shouldn't they have copied the formula at some point???

Select Cell A3
Select the columns A:C
F5 - Special... | Blanks
(all blank cells should now be highlighted)
Type = <up arrow
Ctrl + Enter
Copy PasteSpecial Value
--
HTH...

Jim Thomlinson


"Jacob Skaria" wrote:

Enter the formula in say J2
=J1

Select column A.
Press F5. Click Special
From GoTo window select Blanks.
Right clickPaste SpecialFormulas..

If this post helps click Yes
---------------
Jacob Skaria


"Dave Shultz" wrote:

I want to write a macro to copy down €œHoliday€� in column A until the row
where €œVacation€� appears. And then copy down €œVacation€� in the subsequent
rows. And the in column B, I want to copy down €œTeam A€� until the next team
is mentioned. However, I dont want the macro applied to the columns where I
have the # of hours listed.

Suggestions?

Type Team Name Jan Feb Mar Apr
Holiday Team A Tom 16 8
Dick 16 8
Harry 16 8 12
Team B Sue 16
Kim 16 8
Sam 16 8
Team C George 16 8
John 16 8
Jim 8
Vacation Team A Tom 8
Dick 8
Harry
Team B Sue 8
Kim
Sam 8
Team C George 8
John 8
Jim

Thanks Jim for pointing that out..

Dave, The below method will work only if the cells are genuine blanks...(if
the data is imported from some other source it may not be the case.)

--Select a unused cell say E2
--Enter the formula in cell E2 which references the top cell (=E1)
--Copy the cell
--Keeping the copy select the data range in ColA and ColB
--Press F5. From Goto window select blanks
--This will select all blanks.. Now Right clickPasteSpecialFormulas. will
fill in with the data you need..
--Once done you can select col A Copy PasteSpecialValues to turn
formulas to actual values

If the above doesnt work then try the below macro..

Sub MyMacro()
Dim lngRow As Long
For lngRow = 2 To Cells(Rows.Count, "C").End(xlUp).Row
If Trim(Range("A" & lngRow)) = "" Then _
Range("A" & lngRow) = Range("A" & lngRow - 1)
If Trim(Range("B" & lngRow)) = "" Then _
Range("B" & lngRow) = Range("B" & lngRow - 1)
Next
End Sub



If this post helps click Yes
---------------
Jacob Skaria


"Jim Thomlinson" wrote:

Shouldn't they have copied the formula at some point???

Select Cell A3
Select the columns A:C
F5 - Special... | Blanks
(all blank cells should now be highlighted)
Type = <up arrow
Ctrl + Enter
Copy PasteSpecial Value
--
HTH...

Jim Thomlinson


"Jacob Skaria" wrote:

Enter the formula in say J2
=J1

Select column A.
Press F5. Click Special
From GoTo window select Blanks.
Right clickPaste SpecialFormulas..

If this post helps click Yes
---------------
Jacob Skaria


"Dave Shultz" wrote:

I want to write a macro to copy down €œHoliday€� in column A until the row
where €œVacation€� appears. And then copy down €œVacation€� in the subsequent
rows. And the in column B, I want to copy down €œTeam A€� until the next team
is mentioned. However, I dont want the macro applied to the columns where I
have the # of hours listed.

Suggestions?

Type Team Name Jan Feb Mar Apr
Holiday Team A Tom 16 8
Dick 16 8
Harry 16 8 12
Team B Sue 16
Kim 16 8
Sam 16 8
Team C George 16 8
John 16 8
Jim 8
Vacation Team A Tom 8
Dick 8
Harry
Team B Sue 8
Kim
Sam 8
Team C George 8
John 8
Jim