Problem in my macro....help please!
Posted by RoB on October 02, 2001 4:16 PM
The following is a small part of my macro. I have a workbook open, and i want it to be able to open the "Credit Log 2001.xls" file, and fill in the next blank cell with the data from "J8" on sheet "Prelim Info" on the first workbook i had open (CurrentbookName). The problem is with my syntax in the bold part in the following:
CurrentbookName = ActiveWorkbook.Name
Workbooks.Open Filename:="S:\Credit Log 2001.xls"
Dim r As Integer
r = 0
Do Until Cells(r + 1, 1).Value = ""
r = r + 1
Loop
ActiveCell = Workbooks("CurrentbookName").Worksheets("Prelim Info").Range("J8")
Whats wrong with this formula?
Thanks for any help in advance :)
Posted by RoB on October 02, 2001 5:08 PM
Another thing...
I found another problem. I want to copy the data from the cell. I dont want to create a link between these workbooks. Also, if i copy the cell, i dont want to paste the format onto the "Credit Log 2001.xls" cell, just the data. IE. If "J8" is 3 columns wide, i dont want it to be 3 columns wide when pasted. I just want it to paste the data.
Thanks again :)
Posted by Robb on October 03, 2001 1:43 AM
Robb
Since CurrentbookName is a variable which represents the name of
the workbook, you do not need the "" when using it.
Try the code without it, like this:
ActiveCell = Workbooks(CurrentbookName).Worksheets("Prelim Info").Range("J8")
Any help?
Regards
Posted by Robb on October 03, 2001 1:47 AM
Re: Another thing...
Rob
If you use the code you posted, amended as per my previous reply, the cell should
receive the value of J8, not a link to it (it is not really a copy so the format
does not change).
Does it work for you now?
Regards
I found another problem. I want to copy the data from the cell. I dont want to create a link between these workbooks. Also, if i copy the cell, i dont want to paste the format onto the "Credit Log 2001.xls" cell, just the data. IE. If "J8" is 3 columns wide, i dont want it to be 3 columns wide when pasted. I just want it to paste the data. Thanks again :)
Posted by RoB on October 03, 2001 11:36 AM
yeah thanks again Robb!
Since CurrentbookName is a variable which represents the name of
: The following is a small part of my macro. I have a workbook open, and i want it to be able to open the "Credit Log 2001.xls" file, and fill in the next blank cell with the data from "J8" on sheet "Prelim Info" on the first workbook i had open (CurrentbookName). The problem is with my syntax in the bold part in the following
