• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with extracting data macro

R

rkscroggins

New Member
Hello, I am trying to create a macro that will extract data from one sheet to another in multiple rows referencing a value in column B. I've attached a condensed version of my file.

I need a macro that will basically do the same thing as a VLookup / XLookup, referencing column B in both sheets, and dump the data into multiple columns. The columns are labeled; the columns labeled "tracker" are not extracted from the project data sheet and can therefore be skipped over.

I need the format, font, borders, etc in the sheet titled "Master" to remain the same. The data in sheet "Project Data" is extracted from a database and is therefore raw data. Ideally I would download new raw data each morning, run a macro to extract that data into "Master" corresponding with columns B.

I have written one successful code, but it merely copies data in order by row. It does not reference columns B and therefore the data is not accurate to each row.

I have written another code, but I continue to get an error and am stuck. I've pasted both codes below. Any help you can give me would be wonderful; I've spent hours going cross eyed over this. :)

First Code that doesn't reference column B:

>>> use code - tags <<<
Code: 
Sub Extract_Data_from_One_Sheet_to_Another()
Sheets("Project Data").Range("I2:I500").Copy
Sheets("Master").Range("E3:E501").PasteSpecial xlPasteFormulasAndNumberFormats
Sheets("Project Data").Range("BX2:BX500").Copy
Sheets("Master").Range("F3:F501").PasteSpecial xlPasteFormulasAndNumberFormats
End Sub

Second code that returns error I have tried to manipulate the code many many times but it continues to return an error

Code: 
Sub CrownWest()
Application.ScreenUpdating = False
Dim i, j As Long
j = Sheets("Master").Range("B3:B501")
i = Sheets("Project Data").Range("B2:B500")
Application.WorksheetFunction.VLookup(Range("B3:B501"), Sheets("Master"), (Range("B2:B500")), Sheets("Project Data"), 0) = 1
Sheet2.Range("I2:I").Copy
Sheet1.Range("E3:E501" & i).PasteSpecial xlPasteFormulasAndNumberFormats
Sheet2.Range("BX2:BX").Copy
Sheet1.Range("F3:F501" & i).PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 

Attachments

  • Master Tracker Draft.xlsx
    60.5 KB · Views: 0
Last edited by a moderator:
rkscroggins
Are You sure that You've pasted Your second code as You've written and tested it somewhere?
Have You tested Your ... VLookup ... line somewhere? eg with worksheet alone?
Its syntax is like Range("H3").Value = WorksheetFunction.VLookup(Range("H2"), Range("B3:E9"), 4, False)
After that Your .copy ... what do You would like to copy? from I2 to ... I ?
Where do You would like to copy E3:E501 ... could be okay, but ...
... but after that &i ( which is Sheets("Project Data").Range("B2:B500")
 
Hello, to be honest I am very new to macros and do not know much so I'm struggling to follow your questions. Please forgive my ignorance and disregard any of my fumbled attempts at writing this myself.

I basically just need a macro that would accomplish what a VLookup would, without having to manually VLookup every column and then PSV. Additionally I need to reference both column B's, whereas a classic VLookup references column A.

Example:
On "Master Sheet", B3, 591951
Find 591951 in "Project Data" column B
Copy City from "Project Data" Column J associated with 591951 (in this case it's row 157), paste to column F in "Master Sheet" while retaining all formatting in "Master Sheet"
Repeat the same function for Zip, Zoning JDX, Zoning Type, etc... with "Master Sheet" B4 onward.

Here would be an example of the desired output below.

To further complicate the matter, I had the incorrect reference columns in row 1 of the "Master Sheet". I have corrected those in the attached file should you need it.

BUN (Pink=2 Separate Apps)App IDSS SAQInternal NotesAddressCityZipZoning JDXZoning TypePermit TypePermitting JDX90% Received Forecast90% Received Actual Date
844919591951Brad22945 COLTRANENEWHALL91321COUNTY OF LOS ANGELES, CAAdmin ReviewBuilding PermitCOUNTY OF LOS ANGELES, CA4/8/20223/1/2022
878053592109Brad12000 Balboa BlvdLos Angeles91344CITY OF LOS ANGELES, CAAdmin ReviewBuilding PermitCITY OF LOS ANGELES, CA4/8/20222/28/2022
855986592068Sam3021 SOUTH FULLERTON ROADROWLAND HEIGHTS91748COUNTY OF LOS ANGELES, CAAdmin ReviewBuilding PermitCOUNTY OF LOS ANGELES, CA3/1/20221/28/2022
845079591982Brad20051-D.2 VENTURA BOULEVARDWOODLAND HILLS91364CITY OF LOS ANGELES, CAAdmin ReviewCITY OF LOS ANGELES, CA3/1/20221/28/2022
 

Attachments

  • Master Tracker Draft.xlsx
    60.6 KB · Views: 1
rkscroggins
If You would like to learn ...
... then try to do it manually ... try to write a formula, which can work?
If You can get this work manually,
then You'll get it work with VBA too.
Or
You gotta ask almost same question again and again.
 
vletm said:
rkscroggins
If You would like to learn ...
... then try to do it manually ... try to write a formula, which can work?
If You can get this work manually,
then You'll get it work with VBA too.
Or
You gotta ask almost same question again and again.
Click to expand...

At this point I just need a working code to put into VBA, I was hoping someone in this forum could write one for me. If you can help that would be great. Thank you for your time.
 
You must log in or register to reply here.
Back
Top