Pasting to Cells to the Right.

[XKruodo]

A sheet contains AC NO, DATE, and Amount of Clients.

116530 17-Feb-10 2000.00
39361 17-Feb-10 300.00
40236 18-Feb-10 350.00
10563 18-Feb-10 100.00
116530 19-Feb-10 250.00

Ac Nos are names of the excel workbook (116530.xls) located at D:\conference\

AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E
Minutes, F Total, G is blank for remarks, H is date and I is Amount.


3-Mar-10 7:37 PM UK 1.00 58 58.00 2-Mar-10 200.00
3-Mar-10 8:46 PM UK 1.00 57 57.00
3-Mar-10 9:30 PM UK 1.00 25 25.00 4-Mar-10 500.00

1. I have to find first empty cell in column A ( A4 here )
2. Then i have to move cursor to H4 to paste data.
3. But if H4 is not empty i need to paste it to H5.

I tried this..

'Position the cursor in the first cell in the client file. Assumes
'client data will be posted beginning in column A. Adjust as required.
Range("A5").Select

' To Find the first empty cell where the current data can be posted.
Do While ActiveCell.Text > ""
ActiveCell.Offset(1, 0).Select
'Assumes Excel sheets have 65536 rows. This prevents running
'off the bottom of the worksheet and causing an error. Adjust as
'required.
If ActiveCell.Row > 65536 Then Exit Sub
Loop

'Paste the client data into the client worksheet file.
ActiveSheet.Paste

What do i need to change here so that i can paste data to empty cell in
column H (Data in H is not contiguous )

 

[Paul Robinson]

A sheet contains AC NO, DATE, and Amount of Clients.

116530  17-Feb-10       2000.00
39361   17-Feb-10       300.00
40236   18-Feb-10       350.00
10563   18-Feb-10       100.00
116530  19-Feb-10       250.00

Ac Nos are names of the excel workbook (116530.xls)  located at D:\conference\

AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E
Minutes, F Total, G is blank for remarks, H is date and I is Amount.

3-Mar-10        7:37 PM UK      1.00    58      58.00           2-Mar-10       200.00
3-Mar-10        8:46 PM UK      1.00    57      57.00                  
3-Mar-10        9:30 PM UK      1.00    25      25.00           4-Mar-10        500.00

1. I have to find first empty cell in column A ( A4 here )
2. Then i have to move cursor to H4 to paste data.
3. But if H4 is not empty i need to paste it to H5.

I tried this..

'Position the cursor in the first cell in the client file. Assumes
'client data will be posted beginning in column A. Adjust as required.
Range("A5").Select

' To Find the first empty cell where the current data can be posted.
Do While ActiveCell.Text > ""
  ActiveCell.Offset(1, 0).Select
    'Assumes Excel sheets have 65536 rows. This prevents running
    'off the bottom of the worksheet and causing an error. Adjust as
'required.
    If ActiveCell.Row > 65536 Then Exit Sub
Loop

'Paste the client data into the client worksheet file.
ActiveSheet.Paste

What do i need to change here so that i can paste data to empty cell in
column H (Data in H is not contiguous )

With Range("A1").End(xlDown).offset(1,0)

 

[Paul Robinson]

Hi
This does your 3 steps

With Worksheets("Client File").Range("A1").End(xlDown).offset(1,0)
'one below end
If Cstr(.offset(0,6).Value)="" then
Something?.Copy .offset(0,6) '6 to right i.e H
Else
Something?.Copy .offset(1,6) '1 below that
end if
End with

I don't know what it is you are copying, so I have left it at
Something? It might be

Workbooks("Master").Worksheets("Mastersheet").Range"B6")

for example

regards
Paul

A sheet contains AC NO, DATE, and Amount of Clients.

116530  17-Feb-10       2000.00
39361   17-Feb-10       300.00
40236   18-Feb-10       350.00
10563   18-Feb-10       100.00
116530  19-Feb-10       250.00

Ac Nos are names of the excel workbook (116530.xls)  located at D:\conference\

AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E
Minutes, F Total, G is blank for remarks, H is date and I is Amount.

3-Mar-10        7:37 PM UK      1.00    58      58.00           2-Mar-10       200.00
3-Mar-10        8:46 PM UK      1.00    57      57.00                  
3-Mar-10        9:30 PM UK      1.00    25      25.00           4-Mar-10        500.00

1. I have to find first empty cell in column A ( A4 here )
2. Then i have to move cursor to H4 to paste data.
3. But if H4 is not empty i need to paste it to H5.

I tried this..

'Position the cursor in the first cell in the client file. Assumes
'client data will be posted beginning in column A. Adjust as required.
Range("A5").Select

' To Find the first empty cell where the current data can be posted.
Do While ActiveCell.Text > ""
  ActiveCell.Offset(1, 0).Select
    'Assumes Excel sheets have 65536 rows. This prevents running
    'off the bottom of the worksheet and causing an error. Adjust as
'required.
    If ActiveCell.Row > 65536 Then Exit Sub
Loop

'Paste the client data into the client worksheet file.
ActiveSheet.Paste

What do i need to change here so that i can paste data to empty cell in
column H (Data in H is not contiguous )

With Worksheets("Client File").Range("A1").End(xlDown).offset(1,0)
If Cstr(.offset(0,6).Value)="" then
.Paste .offset(0,6)
Else
.Paste .offset(1,6)
end if
End with