Replies

Thanks HTH. I’ll definitely give that a try tomorrow. One question though is that each time I run this, it is going to be a different file name where I want to paste stuff. The one I copy from will always be the same. Is there a way for me to get the name of the workbook where I am going to paste stuff via the VBA? That will always be the file that is open and from where I run the macro. I guess I’m looking for something like – ‘Set wkbDest = the current file that is open’ in place of your line of code that says, ‘ Set wkbDest = Application.Workbooks.Open(Filename:=”D:PATHFILENAME.EXT’
I think that might be my last hurdle unless it doesn’t copy formulas but only copies the values…I know I can’t use xlPasteValues when I need to use xlPasteFormulas.

Wow, you guys took my original question to a completely different level. For my purposes, the macro in Personal Macros is a good solution. However, I have run into another snag to which you will probably know the solution.
Sometimes when I try to copy data from one file to the new one, I want to use PasteSpecial for formulas or column widths, etc. The problem is that I always get a run time error 1004 and a message saying the PasteSpecial method of the range object failed (or something to that effect). From what I’ve read, it may be because after I copy the material, instead of activating the file where I’m going to paste it, I close the one from which I’m copying (therefore going back to the place I’m going to paste it). Is that the problem? If so, do I need to just leave the file open and refer to the two files by their names?

In psuedo code –
originalFile – copy range (A1:BU3)
close originalFile
Select A1:BU3 and PasteSpecial

always generates an error

OK, thanks again. That should be easy enough.

I guess I need a little more help. I have a xlsm file open and recorded a macro in it just to get my personal.xlsm file started but when I go to the path you suggested, I see a lot of other files there that have macros in them but I don’t see one there named personal or XLSTART. Do I need to create that file before trying to record a macro in it? When I go to record a macro, I choose to Store in Personal Macro Workbook. What am I missing?

Cool. So I can open the worksheet I download and run the macro right from there because it will be able to see the macro in that location? That will do the trick. Thanks so much…

Thanks so much. I figured out what I was doing wrong on the Mac and it now works as I expected. I’m using two columns and the first has a validation list on it so I choose the category with that dropdown and then there is a vlookup function in the second column that gets the ID based on whatever was chosen in the first column.

Zeddy, It works on my PC at home but it isn’t really what I was looking for. I got it to work the way I want by using two columns but I was hoping to do it in one. Unfortunately, it works fine on a PC but I can’t get it to work on the Mac at work. I don’t know if there is a work-around or not. I’ve attached my solution so you can see what I’m after. If you click the arrow in column N, then choose a category, the ID will pop into column O. I wanted the ID to replace the category in N because I don’t need the category title, just the ID but I can make it work like this with two columns if I have to…

Thanks Zeddy, I’m at work where they have me working on a Mac and some of your features are not supported on a Mac so I’ll have to try it again when I get home this evening. Thanks. I’ll post here how it goes.

Maud, the Amazon site is on sellercentral but it belongs to a business owner and is his inventory with his login information. What I have now works really well and saves me hours of time. I would be interested in knowing how to extract data right from a web page though…

I did delete the boxes and there were also some checkboxes and comboboxes on the worksheet so I just deleted all the objects. I didn’t need an if TypeOf section since I deleted them all. Thanks though.

I will give that a look. Thanks for your suggestion.

Yes, that is rather strange. Your file worked fine for me too. I only changed a little bit of your code as you can see. Perhaps I had a typo at first. Bottom line, it works now and saves me hours of time. Thanks for your help. J. P. Zinn

I could just do an INSERT statement with the fields I need from there. The trouble is, there are several of those web pages for each age group and each region (Georgia (in my case), Southern, National). If I could automate the gathering of the data and pasting it into Excel, I could write a macro from there to create my INSERT statements. I’ll keep thinking about this. It isn’t something I have to do right now, just a long term project for my own sense of accomplishment. Thanks for your ideas.

I finally got it to work. My code is very similar to the suggestion from Zeddy but there are some differences. Nonetheless, thanks to all who contributed. Apparently there are two types of textboxes that can be in Excel – one from the Drawing toolbar, and one from the control toolbox toolbar (these happened to be the latter). My solution is below:

Dim OLEObj As OLEObject ‘from the control toolbox toolbar
Dim DestCell As Range
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is msforms.TextBox Then
Set DestCell = OLEObj.TopLeftCell.Offset(-1, 0)
DestCell.Value = OLEObj.Object.Value
End If
Next OLEObj
End With

That looks great but when I run it, I get a message saying, “Unable to get the TopLeftCell property of the OLEObject class”. I wonder if the textbox pasted from a web page is an OLEObject. When I look at the properties of the textbox from the developer tab in design mode, it says “HTMLText1 HTMLText” at the top in the combobox that contains the list of objects on the sheet.

I had not tried to paste the web information into Notepad+ but have since tried that and it does not include either the textbox or the value in it when I paste it there.