Posts by structo

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

Quote from cytop;777271

.

you should learn first how to behave with fresh users. i am asking about my post regarding "why my post is not solved" yes i am asking.because i have doubts regarding any other hidden settings in this forum to visible my post. dont blame any body.you seems like a un talented and lack of leader skills. please improve managing skills as soon as possible.

Bye.

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

Quote

Dear structo,

You have received an infraction at Free Excel\VBA Help Forum.

Reason: Cross Posted
-------

-------

This infraction is worth 5 point(s) and may result in restricted access until it expires. Serious infractions will never expire.

Display More

with reference to your PM, i confirmed you are applied revenge upon me.due to criticizing.

this is not correct way. you should guide for new users. dont do like that.

your main attention is point out pin to pin points instead of solving users posts. then why asking questions regarding my posts without solving my post.you are not share single your code attempt.only asking questions.if you not able to take challenge please dont waste my time for asking un useful questions.

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

i have already provided exact data against your questions.

i have described as, i have 3 different files, those are file1,file2, and file3. and i need to copy from those 3 files with different data range as shown in post#5 and these range data paste to one Target file (destination file) at "Masterdata" sheet name as described at post#5.

[TABLE="width: 912"]

Data File Names

Full Path

Data Range Start Cell

Data Range End Cell

Copy To Sheet name (@destination file sheet)

CopyToLocation(Start Cell Only)

File1.xlsm

E:\work\

$AF$2

$AI$4

MasterData

$A$2

File2.xlsm

E:\work\

$AC$2

$AD$3

MasterData

$A$5

File1.xlsm

E:\work\

$V$3

$V$40

MasterData

$A$7

File2.xlsm

E:\work\

$Y$3

$Y$40

MasterData

$B$7

File3.xlsm

E:\work\

$L$3

$L$40

MasterData

$C$7

[/TABLE]

i have cleared all your questions but where is your solution?instead of questions.

my thread at another site is http://www.excelforum.com/exce…ent-files-code-error.html

Thanks.

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

Quote from cytop;777249

I have no idea what you mean by that, but it doesn't describe how to replicate the error.

Well nice questioning job friend instead of providing solutions.keep it up. i have observing from my 1st post. you asked only questions without providing solutions.

and every time demanding about "Hire Help" i think this is not a correct way. kindly try to solve questions in this forum instead of asking questions one after one.

Thank you.

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

Quote from cytop;777179

You need to explain the steps to reproduce the error.

Dear sir,
thank you for asking questions. please observe my problem with code at post#1.i need process as per attached pic. please find. with my code producing wrong and merging data while doing as per attached pic procedure.are you checked my code? please fix the errors or modify the existing code.

Thanks.

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

Quote from cytop;777158

No idea... try at least listing the code.

Thank you for response. here is my code: please modify for perfect working:

Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String


Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String
    
    strListSheet = "List"
    
    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select
    
    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""
        
        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
        strWhereToCopy = ActiveCell.Offset(0, 4).Value
        strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
        
        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
        Set dataWB = ActiveWorkbook
        
        Range(strCopyRange).Select
        Selection.Copy
        
        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select
        
        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
        dataWB.Close False
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub
    
ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
End Sub


Public Function LastRowInOneColumn(col)
    'Find the last used row in a Column: column A in this example
    'http://www.rondebruin.nl/last.htm
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
End Function

Thank you.

Hi friends,

In my macro file produced wrong results. values are produced correctly up to 3 items. if i add more items (up to 5 or 6 items), produced wrong values at "MasterData" Sheet.

what are the errors in my code? please make as a perfect code or make a fresh code.

Thanks in advance.

Re: Convert My Formulas to Code Version

Quote from sktneer;776927

Please find the full code....

Your Code is really fulfills my requirements Thanks a Lot Friend. i will test with large data and i give feedback.

Thanks,
Best Wishes.

Re: Convert My Formulas to Code Version

Quote from jaslake;776904

Hi structo

This Code in the attached will deal with Columns K -> O. See if you can apply the Code to Columns P -> X.

CTRL + x will fire the Code. Need help let me know.

Dear friend your code is rocking,

i have tried for O P V columns with help of your code but getting errors.I have prepared like this

.Range("P4").FormulaArray = "=INDEX(L4" & LRA & ":M4" & LRA & ", MATCH(MAX(ABS(L4" & LRA & ":M4" & LRA & ")), ABS(L4" & LRA & ":M4" & LRA & "),0))"

i am faced problems with "INDEX" formulas remaining columns like O and V kindly solve.i have no idea about coding.

Thanks,
Best regards.

Re: Convert My Formulas to Code Version

THANK YOU FRIEND..I Will try for remaining columns and give feed back.

Re: Convert My Formulas to Code Version

Dear cytop,
i just saw many posts in this forum. comparing to other posts my post is simple. many posts are solved and they are hard comparing to my post.

and i have already tried by Classical formulas. so almost 100% done by formula version. i need paste those formulas to remaining rows ( if My data range is increases) with help of macros.

So kindly solve my post.

Thanks in advance.

Re: Convert My Formulas to Code Version

My Sample output file without formulas.

Please find the attachment, please start from scratch yours brilliant code.

Thanks,

With Best Regards.

Dear Friends,

I am New to this forum and VBA.
I have a Excel sheet with Array formulas. i need VBA or Macro Version. setting of ranges and copying formulas are very difficult(because i had Thousands of rows). so kindly convert my Formula Excel sheet to Xlsm sheet.

Formulas paste rule is Look upto A:G range Last Cells data range and formulas are Paste to K to X range.because present i have written as range up to A4 to A21 at L4 cell. but requirement rule is automatically developed range with respect to look up, upto End data of A column Cell.
please find Sample Attachment.

Thanking you in Advance.

With Best regards