• 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.

Fetching Data from other worksheets

Zach

Member
I've looked through the forum looking for a subject similar to what i'm trying to accomplish but I can't find anything that gets me off in the right direction.

I have created a fairly large file that is basically a database holding information on multiple tabs, each tab is a has a different category of information on it (OPTION MASTER REBOOT). I was given another file (New Community Plan index) and asked to find a way to pull information from the OPTION MASTER REBOOT to the New Community Plan Index.

Inside the New Community Plan index file on tab "Plan 1", information is input in column A starting on row 22 and moving down. What I'm looking for is a macro that can be run to look at the option number from column A match it to the option number in the OPTION MASTER REBOOT and fill in the description columns G-L. If it is helpful the first 3 letters in the option number will match to a tab on the OPTION MASTER REBOOT.

Basically I need to know of this is even possible, this is way way out of my vba league and I figure trying to create a super-formula opens too many possibilities of it breaking somewhere.

Please look over the two files and let me know if anyone has any suggestions.
 

Attachments

  • New Community Plan Index.xlsm
    365.6 KB · Views: 3
  • OPTION MASTER REBOOT.xlsx
    193.8 KB · Views: 3
Yea I don't think I'm that complex, but I think I've found a small work around for my issue. However my macro is bugging out and I can't figure out where my error is. My error says my object variable or with block variable is not set. What did I miss?

Code:
Sub mergedata()
'Macro Declarations
Dim wrk As Workbook
Dim Mtab As Worksheet 'Summary Worksheet
Dim rng As Range 'Range Object
Dim colCount As Integer 'Column count in tables in worksheets
Dim sht As Worksheet
Dim CVT As Worksheet
Set CVT = Worksheets("ACS")

'create new worksheet and name
Set Mtab = Sheets.Add(Before:=CVT)
With Mtab
    .Name = "Summary"
    .Range("A1").Value = "Option Code"
    .Range("B1").Value = "Description 1"
    .Range("C1").Value = "Description 2"
    .Range("D1").Value = "Description 3"
    .Range("E1").Value = "Description 4"
    .Range("F1").Value = "Description 5"
    .Range("G1").Value = "Description 6"

Range("A1:G1").Select
    With Selection.Interior
        .color = 12419407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .Bold = True
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

'Start loop
For Each sht In wrk.Worksheets
    If sht.Index = wrk.Worksheets.Count Then
        Exit For
    End If
        'Data Range in Worksheet - starts from 2nd row as 1st rows are header rows in all worksheets
    Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
    'Put data into the Master worksheet
        Mtab.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    Next sht
     'Fit the columns in Master worksheet
    Mtab.Columns.AutoFit
    Mtab.Rows.AutoFit
         
     'Screen updating should be activated
    Application.ScreenUpdating = True
   
    End With
   
End Sub
 

Attachments

  • OPTION MASTER REBOOT.xlsm
    204.4 KB · Views: 7
Back
Top