Find Empty Column and paste cell values
 

I have a question that I hope someone can help
I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", in worksheet
then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2, (a1:f1)

thanks for the hel

mike

Find Empty Column and paste cell values
 

worksheets("Sheet2").Range("A1:F1").Value = _
worksheets("Sheet1").Range("A1:F1").Value


Dim rng as Range
for each cell in Range("A1:IV1")
if application.countA(cell.EntireColumn) = 0 then
set rng = cell.Entirecolumn
Exit for
End if
Next
if not rng is nothing then rng.Select

--
Regards,
Tom Ogilvy


Mike wrote in message
...
I have a question that I hope someone can help.
I want to write a Macro that finds the first empy column in Range
(A1:IU),cells A1:F1=" ", in worksheet2
then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2,
(a1:f1).

thanks for the help

mike

Find Empty Column and paste cell values
 

Tom
Thanks for responding but I am still having trouble with the program
I will rewrite the question and maybe you can see what I am not understanding.

I want to write a Macro that finds the first empy column in Range
(A1:IU),cells A1:F1=" ", in worksheet2** the whole column does not have to be empty just the
first 6 rows.
then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2,
(a1:f1). ( having trouble copying the cells in worksheet 1(A1:f1) to the empty cells
(a1:f1) IN worksheet 2
I am using worksheet 2 as a database worksheet, so it is important to save the data in worsheet 1.

Also, how do the below statements fit into the subrouutine
worksheets("Sheet2").Range("A1:F1").Value = _
worksheets("Sheet1").Range("A1:F1").Value

I hope you can figure out what I need to do.

thanks for your help
mike
----- Tom Ogilvy wrote: -----

worksheets("Sheet2").Range("A1:F1").Value = _
worksheets("Sheet1").Range("A1:F1").Value


Dim rng as Range
for each cell in Range("A1:IV1")
if application.countA(cell.EntireColumn) = 0 then
set rng = cell.Entirecolumn
Exit for
End if
Next
if not rng is nothing then rng.Select

--
Regards,
Tom Ogilvy


Mike wrote in message
...
I have a question that I hope someone can help.
I want to write a Macro that finds the first empy column in Range
(A1:IU),cells A1:F1=" ", in worksheet2
then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2,
(a1:f1).
thanks for the help
mike

Find Empty Column and paste cell values
 

then paste the current values in Worksheet1,cells (A1:F1) to
Worksheet2,
(a1:f1). ( having trouble copying the cells in worksheet 1(A1:f1) to
the empty cells
(a1:f1) IN worksheet 2
I am using worksheet 2 as a database worksheet, so it is important to
save the data in worsheet 1.

Also, how do the below statements fit into the subrouutine
worksheets("Sheet2").Range("A1:F1").Value = _
worksheets("Sheet1").Range("A1:F1").Value

the above code does what you state.

I hope you can figure out what I need to do.
I tried - but most of what you write sounds like blither speak.
for instance:

I want to write a Macro that finds the first empy column in Range
(A1:IU),cells A1:F1=" ",

A1:IU - what is that supposed to mean? IU is a column Reference, A1 is a
cell reference. I assumed you wanted the the last column which is IV and
the first row A1:IV1

What is [,cells A1:F1=" "] supposed to mean in that context. Assumed A1:F1
are empty cells - but what is the significance. If you want the first empty
column after F1, then why say A1:IU, say G1:IV1 or whatever you mean.

for each cell in Range("G1:IV1")
if application.countA(cell.Resize(6,1)) = 0 then
Cell.Resize(6,1).Select
exit for
end if
Next

Note that a cell that has a space in it is not empty.


--
Regards,
Tom Ogilvy



"Mike" wrote in message
...
Tom
Thanks for responding but I am still having trouble with the
program
I will rewrite the question and maybe you can see what I am not
understanding.

I want to write a Macro that finds the first empy column in
Range
(A1:IU),cells A1:F1=" ", in worksheet2** the whole column does not
have to be empty just the
first 6 rows.
then paste the current values in Worksheet1,cells (A1:F1) to
Worksheet2,
(a1:f1). ( having trouble copying the cells in worksheet 1(A1:f1) to
the empty cells
(a1:f1) IN worksheet 2
I am using worksheet 2 as a database worksheet, so it is important
to save the data in worsheet 1.

Also, how do the below statements fit into the subrouutine
worksheets("Sheet2").Range("A1:F1").Value = _
worksheets("Sheet1").Range("A1:F1").Value

I hope you can figure out what I need to do.

thanks for your help
mike
----- Tom Ogilvy wrote: -----

worksheets("Sheet2").Range("A1:F1").Value = _
worksheets("Sheet1").Range("A1:F1").Value


Dim rng as Range
for each cell in Range("A1:IV1")
if application.countA(cell.EntireColumn) = 0 then
set rng = cell.Entirecolumn
Exit for
End if
Next
if not rng is nothing then rng.Select

--
Regards,
Tom Ogilvy


Mike wrote in message
...
I have a question that I hope someone can help.
I want to write a Macro that finds the first empy column in Range
(A1:IU),cells A1:F1=" ", in worksheet2
then paste the current values in Worksheet1,cells (A1:F1) to
Worksheet2,
(a1:f1).
thanks for the help
mike

Find Empty Column and paste cell values
 

To
Thanks for the help but I am still having problems. I going to give an example and then maybe you ca
see where I am going wrong.
Example, Worksheet 1, cell A1= bob, cell A2 = mike, cell A3= jim
I want the macro to goto Worksheet 2 and find the first column where the cells a1 through a3 are empty
Then I want the macro to copy the contents of Worksheet1, a1:a3 into the empty cells (A1:A3) in the selected column i
Worksheet 2.
I was also having trouble understanding the copy commands (copying Worsheet 1 A1:a3 into the selected empty cells in Worksheet 2) when the code that you sent to me finds the firs
empty column and exits the for-next loop

I appreciate your helping through this Macro. Once I am able to get this Macro working, I pla
to use it on many other applications

Mike

Find Empty Column and paste cell values
 

Much clearer:

Sub copyData()
Dim rng As Range, cell As Range
On Error Resume Next
With Worksheets("Sheet2")
Set rng = .Rows(1).SpecialCells(xlBlanks)
End With
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each cell In rng
If Application.CountA(cell.Resize(3, 1)) = 0 Then
Worksheets("sheet1").Range("A1:A3").Copy _
Destination:=cell
Exit For
End If
Next

End Sub

Worked for me based on your description.

--
Regards,
Tom Ogilvy


Mike wrote in message
...
Tom
Thanks for the help but I am still having problems. I going to give
an example and then maybe you can
see where I am going wrong.
Example, Worksheet 1, cell A1= bob, cell A2 = mike, cell A3= jim.
I want the macro to goto Worksheet 2 and find the first column where the
cells a1 through a3 are empty.
Then I want the macro to copy the contents of Worksheet1, a1:a3 into the
empty cells (A1:A3) in the selected column in
Worksheet 2.
I was also having trouble understanding the copy commands (copying
Worsheet 1 A1:a3 into the selected empty cells in Worksheet 2) when the code
that you sent to me finds the first
empty column and exits the for-next loop.

I appreciate your helping through this Macro. Once I am able to get this
Macro working, I plan
to use it on many other applications.

Mike

Find Empty Column and paste cell values
 

Tom:
Took a Christmas break. Hope yours was a happy and merry one!

Anyway, thanks for the reply, but still not able to get the macro to work.
I am using Excell 2000. I made a macro and copied your instructions into the macro
as you sent them to me. Tried running the macro from the macro - run macro selection
and also from a macro button assigned to the macro.
Both ways produced no results at all. No copying of worksheet 1, a1:a3 to the the worksheet 2.

Not sure what I am doing wrong but I have appreciated all your help.

Mike