code to copy data from dif.cells in a sheet to a single row in another workbook.

[saziz]

Hi,
I have a workbook ("CaTr") Sheet1 has data ranging ("B2:I41").
There are about 30 cells values (scattered) which needs to be copied in a workbook "CA_Log" in one single next available row .

As soon as the CaTr.sheet1 is filled I want the operator to click a button to trigger this event. Then the sheet1 in workbook CaTr should be saved as "G3" cell value.

I would be greatful if someone can help me on this.
Thank you
Saziz

[Leith Ross]

Hello saziz,

It would help to know which cells you need copied from the range B2:I41.

Tahnks,
Leith Ross

[saziz]

Hello Leith Ross,
Following are the cells:

I2,C3,J3, J4,E7 to 11, D14,15, F14,15, E17, F17,
G17, D20, 21, G19,20,21, I19, J21, B25:I25, B26:I26


Thank you for your help.
Saziz

[Leith Ross]

Hello saziz,

Does the workbook CA_Log have only one sheet?

Thanks,
Leith Ross

[saziz]

Hi Leith Ross,
CA_Log has several sheets. The sheet data is going to go in is called 'CA-Sl'
The data can go on next available row.
Thank you
Saziz

[Leith Ross]

Hello saziz,

Here is the macro. You can place a Forms button on CaTr Sheet1 to call this macro. It will save this sheet as a new workbook using the contents of G3 as the file name. Both workbooks must be open when the macro is run or it will fail. Copy this macro to a Standard VBA module.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[saziz]

Hello Leith Ross,
Thank you. It is working. But I had to disable the last line of code where it is asking it to save as ( WksSrc.SaveAs Filename:=WksSrc.Range("G3").Text.) If I do not disable it, it gives me a 400 error.
However I would like to save that particular sheet as "G3" value in the same workbook.
That means I will always input data in sheet1, transfer the data through code to LA-Log and save that sheet1 as "G3" in the same work book.

Hope I am clear.
Thanks a bunch
Saziz

[Leith Ross]

Hello saziz,

Add this code to bottom of the macro to copy Sheet1 and name it based on G3's value.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[saziz]

Hi Leith Ross,
It is working fine, thank you so much. I might need some help later when I modify the sheet. But it would be later.
Once again I appreicate very much you kind help.

Saziz

[saziz]

Hi Leith,

Two things:
I am trying to put a condition before the data is transfered to WksDst sheet.
the condition is, if on WksDst sheet the value of last cell in column A is equal to value of WksSrc.I2 then exit and msgbox ("Data is being duplicated")
How can I do that.
I tried like this:

Please Login or Register  to view this content.

How can I ask the code to terminate, I don't know.
Second thing is:
The WksDst which is "CA_Log.xls", I have added in the begening of the code to open this workbook. Now how can I say in the code if its already open just activate it.

Please guide me.
Thanks
saziz

[saziz]

Lieth,
Whenever you get a chance can you please look into it.
Thanks
Saziz

[Leith Ross]

Hello saziz,

If you want to exit the macro then your code will look like this...

Please Login or Register  to view this content.

The code at the beginning of the macro sets the worksheet object variable to Workbooks("CA_Log.xls").Worksheets("CA-Sl"). If the workbook isn't already open or the sheet isn't found in the workbook then you will receive an error message.

To activate the destination worksheet use the following code...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[saziz]

Hi Leith,

Thank you for the help. I suceeded in stopping the duplicate entries.
But the following is still an issue:

If Workbooks("CA_Log.xls").Open = True Then
CA_Log.xls .Activate
End If

It give me 438 error "object doesn't support porperty or method"

Thank you
Saziz

[Leith Ross]

Hello saziz,

You can check for an open workbook either by looping through the Workbooks collection or trying to reference it and trap the error if it isn't open.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[saziz]

Thank you Leith.
It solved the problem.

Sincerely
Saziz

[StartingOut]

Hi, I am new here and hope I can get some help, I have read several topics on the form and I looks like this is the place for help.
I have a workbook with 400 sheets called "Lot 1" to "Lot 400 and and sheets "Lot A" through "Lot Z" and lastly sheets "CAB 1" through "CAB 30" On each of these sheets which are Invoices, are the respective Lot, or CAB number in the same cell on each sheet (D12) and on each sheet there is a dolar value representing money owed in cell (K49) My question is how can I have Cell D12 and K49 be sent to a sheet I called AccountsReceivable only if cell K49 has a $value greater then 0. I need the contents of D12 and K49 to be side by side in column A & B on AccountsReceivable sheet