Run-time error '5' when copying range of cells to different sheet

**Meriwether** · 06-26-2014, 05:18 PM

Hey everyone - first post here, so be gentle.

I've got two sheets, the source which is a inventory list - column H is the amounts of line items being used, and the requisition list which is supposed to collect those line items that have been used (i.e. have a value in column H). I've tried doing this with the following code but get a run-time error when I step through past the Range(Cells(... line.

Anything stand out...? Thanks in advance for any help.

M

Please Login or Register  to view this content.

**HaHoBe** · 06-27-2014, 12:04 AM

Hi, Meriwether,

Please Login or Register  to view this content.

is causing the run-time error as Cells expects the range either as a numeric (Cells(1) stands for the first cell in the range) or by row number and column number / character (Cells(1, 1) and Cells(1, "A") refer to Range("A1")). You try to use a string with multiple entires as a reference to range. And you would not need to Select a range prior to copying it.

If you could point out which cells you want to copy over I´m sure we may help you with that code as well if you need help on it.

Ciao,
Holger

**Meriwether** · 06-27-2014, 11:41 AM

Thanks Holger! Man, this chat really is fast. What people say is true!

The code needs to loop through all line items in Sheet1 (~1300 of them) and for the ones with a value in column H (i.e, where Quantities have been filled in) I'd like the corresponding cells in columns A, C, E, H, & N for each line item to copy over to Sheet2.

Really, the only cell I need copied is the item name for each line item in column H. All others I could do through a simple in-cell VLOOKUP with the name as reference. Although, learning how to put it all into a Macro would be cool.

**HaHoBe** · 06-27-2014, 11:59 AM

Hi, Meriwether,

if you want to copy only Column H:

Please Login or Register  to view this content.

for more columns you may take an approach like this:

Please Login or Register  to view this content.

Ciao,
Holger

**Meriwether** · 06-27-2014, 01:12 PM

Thanks Holger -

I can't seem to get either of these to work. For the copying of only column H I'm almost getting it. Only problem was my mistake: the correct cell to be copied should have been from column E... The value is copied correctly, but it is from the wrong cell. I've tried to figure out how to use your code and specify the correct cell but can't figure it out.

The second code you posted does something, but I don't know what. It seems that formulas get copied... I'm probably not giving you enough or correct info. I don't know what half of the formulas you use in this one are, so I'm going to have to spend a bit more time on that before I have something of value to say.

Can I share screenshots with you, or the file itself? Feels like I've got a whole lot of stuff going on here that I'm not communicating: columns A, C, and D in sheet2 are dropdown lists linked to a master inventory list, and some of the cells I'm interested in copying have formulas in them.

Vielen dank.
Carl

**HaHoBe** · 06-27-2014, 01:20 PM

Hi, Meriwether,

either

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

should work for Column E.

You should make clear if you want formulas and formats copied over or just the values of the cells.

Ciao,
Holger

**Meriwether** · 06-27-2014, 02:31 PM

Thanks again man... It works, although I can't figure out how to copy only values over...

**HaHoBe** · 06-27-2014, 02:42 PM

Hi, Meriwether,

Please Login or Register  to view this content.

will copy formulas and formats,

Please Login or Register  to view this content.

will copy the value.

Ciao,
Holger

**Meriwether** · 06-27-2014, 02:55 PM

Terrific! Oddly enough, the code seems to pull line items without values in the H column as well as the ones with values... I tried clearing the column using clear contents, but once I did that the code stopped working altogether.

In the pic, some of the items in the 4th column from the left has quantities, but not others. I don't want to copy over ones that don't have a quantity. That's also going to clear up the #VALUE! errors.

Excel1.JPG

ED: I figured it out. Apparently, I couldn't clear contents on the entire column. Had to clear the buggers out one by one. Thank you Holger - this excel inventory list is working beautifully now.

Run-time error '5' when copying range of cells to different sheet

LinkBack

Thread Tools

Rate This Thread

Display

Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Re: Run-time error '5' when copying range of cells to different sheet

Thread Information

Users Browsing this Thread

Similar Threads

#REF Error When Copying A Sheet In A Sum Range

Copying Data error from master sheet based on Range criteria

Copying Sheet - subscript out of range error

[SOLVED] Run time error '4605' while copying excel range as picture in Wor

[SOLVED] Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined

Bookmarks

Bookmarks

Posting Permissions