VBA for Excel - number format : Accounting

[bzsurf03]

I am working with two Excel workbooks. I need to pass data from a particular field in one workbook to another field in another workbook. In both workbooks, the cell is number formated as Accounting, which puts a little spin on things. I want to capture this Accounting field in a variable (What type of variable?) and pass it on to the other workbook. I am getting various errors no matter what I try. Could somebody help me out with maybe a little code as well? Thanks in advance.

 

[PHV]

Something like this ?
Workbooks("source.xls").Worksheets("Sheet1").Range("A1").Copy _
Destination:=Workbooks("dest.xls").Worksheets("Sheet1").Range("A1")

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bzsurf03]

Thanks for the advice. This looks good to me. I look forward to getting a chance to try it tommorrow. I'll let you know how it works. Thanks again.

 

[bzsurf03]

Still not quite there. I am getting a run-time error 1004; application defined or object defined error. Here is what I am trying to do below. This loop is plugging each value in the loop into a different worksheet of another workbook. Can you see what I need to change to get rid of this error. While debugging, the error occurs in the "Copy" line. Thanks in advance.

Dim Cell As Object
For Each Cell In Range("A1", "A30")
strDestType = Cell.Offset(0, 0)
Cell.Offset(0, 1).Select
Workbooks("source.xls").Worksheets("any").Range(Selection).Copy _
Destination:=Workbooks("Dest.xls").Worksheets(strDestType).Range("H13")
Next

 

[TonyJollans]

Hi bzsurf03,

Don't try to qualify the Selection, just use it ..

[blue]Selection.Copy ....[/blue]

Better yet, don't Select it in the first place, just copy the range you want ..

[blue]Cells.Offset(0,1).Copy ....[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[bzsurf03]

That was helpful and is the direction I need to head, but I am now getting a message that I "cannot change part of a merged cell." I don't know what this means and the help didn't make much sense to me. My changed code is below.

For Each Cell In Range("A1", "A30")
If Cell = "" Then
Exit For
Else
strName = Cell.Offset(0, 0)
Cell.Offset(0, 1).Copy Destination:=Workbooks(strDestName).Worksheets(strName).Range("H13")
End If
Next

*The strDestName is being populated in earlier code

Thanks for any help.

 

[bzsurf03]

It looks like the cell that I want to send this data to is actually a group of merged cells preformatted in the output file. The merged cells are still called H13 in Excel as I am using in my code. Why would this be a problem?

Thanks

 

[AdrianScaife]

the problem comes when you try to copy to H14 which does not exist as a cell in its own right.

 

[TonyJollans]

Hi bzsurf03,

Merged cells give so much trouble. You could try this ..

[blue]Cell.Offset(0, 1).Copy Destination:=Workbooks(strDestName).Worksheets(strName).Range("H13")[red].MergeArea[/red][/blue]

I think it will work even if there aren't any merged cells.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[bzsurf03]

Solution: I unmerged the cells in the output file and waited until after the field was populated and coded a merge of the range. This has removed the original formating though. I guess I need to code in the appropriate number format and font because they seem to reset when you merge the range. Any input is appreciated.

 

[bzsurf03]

Thanks Tony I wish I was updating this page more often or else I would have tried your idea. I have already made so many changes so that I could get it to work. I'll try to go back to where I was and use your idea. I have never seen that used before. Thanks.

 

[bzsurf03]

Tony. It worked! Thanks.

Thanks everyone!