Copy and Paste error - VBA one sheet to another

Guttmann1983

Board Regular
Joined
Jul 27, 2015
Messages
181
Hi, I need to copy and paste data from column B to sheet "Data" at row A500. Caveat is that rows a1-a400 or so have data in there so I am running into an error. Below is the code I have that is giving me an error.

Sub Macro1()
Dim lr As Long
lr = Range("C" & Rows.Count).End(xlUp).Row
Range("B2:B" & lr).Formula = "=A2&':'&C2"
Range("E1:E" & lr).Formula = "=IFERROR(VLOOKUP(A1,Data!A:C,2,FALSE), ""NOT FOUND"")"
Application.CalculateFull
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("A500").Select
ActiveSheet.Paste
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
What is the error ?
This line
Code:
Range("B2:B" & lr).Formula = "=A2&':'&C2"

seems wrong, what is the formula doing AND the quotes marks inside the formula don't make sense !
You also appear to be trying to copy an entire column to A500 which can't be done !!
 
Upvote 0
Whoops, I meant to paste the older version which is "=A2&C2" - Combining them... then pasting only the cells that have data to "Data" worksheet. Basically, I am trying to add onto my vlookup spreadsheet by adding new variables because I was running into problems with vlookup returning the first match it found.. and giving me info from column C... I need column C even though there are multiple instances in column A.

Example:

LOOKUP1 ATTORNEY1
LOOKUP2 ATTORNEY2
LOOKUP3 ATTORNEY3
LOOKUP1 ATTORNEY4

I was trying to combine A and C, then bring that data to the lookup sheet so that I could then put in a new lookup of A&C which will return the correct, ATTORNEY4 value... does that make sense?
 
Upvote 0
My other question was......why are you trying to copy ALL of Col "B" to A500 on the data sheet....that can't be done !!!

does that make sense?

No, it doesn't !
 
Upvote 0
Okay, well I need the VBA to copy only the cells in column B that have text in them to sheet "Data" and add onto the data I have there.
 
Upvote 0
In that case your VBA will have to either filter the data or use a For / Next Loop
If that's the case, do we still need the lines that input the formulas in the code as well ??

Also, do they specifically need to start at row 500 or just the last row plus one on the data sheet ?
 
Upvote 0
Yeah I need the A+C so that I have a lookup value that is different than just A by itself. (Since there are multiple A values that are the same and I need it to match the attorney... I am getting the return value of only the first match on column A... which makes sense with vlookup.. I am just trying to find a way around it)
 
Upvote 0
Maybe this then
Code:
Sub MM1()
Dim lr As Long, lr2 As Long
lr = Range("C" & Rows.Count).End(xlUp).Row
lr2 = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & lr).Formula = "=A2&C2"
Range("E1:E" & lr).Formula = "=IFERROR(VLOOKUP(A1,Data!A:C,2,FALSE), ""NOT FOUND"")"
For r = 2 To lr
If Range("B" & r).Value <> "" Then
        Sheets("Data").Range("A" & lr2 + 1).Value = Range("B" & r).Value
    lr2 = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
End If
Next r
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top