VBA to copy data from list to specific worksheets

nguerra

New Member
Joined
Oct 1, 2013
Messages
45
Hi Everyone, I have some VBA code that searches data in a large table from one sheet and copies specific from that table to another sheet based on a list. The problem that I'm having is when the data is a number, the script seems to fail even if I change the data to text. The VBA code is at the bottom. When you run this code, the first two tabs populate just fine but the third item in the troubled list causing the script to crash. What can I do to have the script match all values in the troubled list? I'd be happy to post the excel file but I don't seem to have that permission.

Sub troubled()
Dim sh As Worksheet, sSh As Worksheet, LR As Long, rng As Range, c As Range, fLoc As Range
Set sh = Sheets("Troubled Items") 'Edit sheet name
Set sSh = Sheets("Master") 'Edit sheet name
LR = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & LR)
For Each c In rng
Set fLoc = sSh.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fLoc Is Nothing Then
fAdr = fLoc.Address
Do
If Sheets(c.Value).Range("A17") = "" Then
fLoc.EntireRow.Copy Sheets(c.Value).Range("A17")
Else
fLoc.EntireRow.Copy Sheets(c.Value).Cells(Rows.Count, 1).End(xlUp)(2)
End If
fLoc.Value = c.Value
Set fLoc = sSh.Range("A:A").FindNext(fLoc)
Loop While fAdr <> fLoc.Address
End If
Next
End Sub

Thanks
Nick
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
nguerra,

What version of Excel and Windows are you using?

I'd be happy to post the excel file but I don't seem to have that permission.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 

nguerra

New Member
Joined
Oct 1, 2013
Messages
45
Sorry Hiker95,

I forgot that one. Excel 2007. I also received a reply from another form (one that I could post an attachment) and got a solution back. Replacing all Sheets(c.value) to Sheets(CStr(c.value)) fixed the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top