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

Updating Two Excel Workbooks with a Macro?

TomNR

TomNR

Member
Hi all,

Hope you can help.

I am wanting to update a separate Excel Workbook with the information found in another Excel Workbook if possible.

The information is tided to an ID number - so basically I want:

1) Find the ID number found in Workbook 1 - F11:F300
2) Grab the information in cells AY11:AY300, BE11:BE300, BK11:BK300 & G11:G300
3) Go to Workbook 2
4) Match the specific ID numbers from Workbook 1 (F11:F300 ) to the ones in Workbook 2 (C10:C1000)
5) Update the data found from Workbook 1 (AY11:AY300, BE11:BE300, BK11:BK300 & G11:G300) into Workbook 2 (AP10:AP1000, AV10:AV1000, BB10:BB1000 & BH10:BH1000) dependant on the ID.

I hope that makes sense and I am sorry for the inexperience but I am stumped and know how good the folks on here are!
 
Hi Tom, and welcome to the forum. :awesome:
Would it be possible to get a sample of your workbooks, so we can see the exact layout? I'm having trouble visualizing what you are describing.
 
Luke M said:
Hi Tom, and welcome to the forum. :awesome:
Would it be possible to get a sample of your workbooks, so we can see the exact layout? I'm having trouble visualizing what you are describing.
Click to expand...

Sure thing:

So basically I want the information found in the Change Control to be put into the cells in the Business Plan at a push of a button.

Sounds simple but I am stumped!
 

Attachments

Hi Tom,

Is the data in the same order each time, or will we need to match line by line?
Are the columns in the same order always?
Your OP had different ranges, such as AY and AP, but your sample workbooks show all the data next to each other. Can you explain?
 
Sorry that was a slimmed down version as the Excel file is huge.

Yes the ranges are far apart and not next to each other, imagine a number of columns inbetween the ones I have sent through.

I am assuming that would mean it would have to be done line by line as the data is spread across multiple columns and not next to each other?

Sorry for the confusion!
 
Will all the rows in Business Plan get updated? Or does the Control workbook have a smaller portion of IDs? Is it possible to have a non-matching idea, and if so, what should happen?

Sorry if it seems like a lot of questions, but with a macro handling this type of transfer, there's a lot of things we'll need to know to get it right. :)
 
Luke M said:
Will all the rows in Business Plan get updated? Or does the Control workbook have a smaller portion of IDs? Is it possible to have a non-matching idea, and if so, what should happen?

Sorry if it seems like a lot of questions, but with a macro handling this type of transfer, there's a lot of things we'll need to know to get it right. :)
Click to expand...

Only the rows with changes from the Control Workbook will get updated in the Business Plan if possible.

The Control workbook will only have IDs that match to the Business Plan, any new IDs are added in manually to the Business Plan.

The number of IDs in the Control workbook vary each time as there may be less Control Changes from one month to the next so it is hard to pin a number on that one.

Let me know if there is any other information and thank you so much for your help.
 
Hi Tom,

That's enough to get the basic macro. You can probably take it from here.
Code: 
Sub TransferData()
Dim wbDest As Workbook
Dim wbSource As Workbook

Dim wsDest As Worksheet
Dim wsSource As Worksheet

Dim fRow As Long
Dim lastRow As Long
Dim recCount As Long

Dim strID As String

Application.ScreenUpdating = False

'Define where our workbooks are. This assumes that this macro exists in the file where info
'is GOING TO
Set wbDest = ThisWorkbook

'Change file path as appropriate
Set wbSource = Workbooks.Open("C:\My Documents\Change Control Form.xlsx")

'Which worksheets are we dealing with?
Set wsDest = wbDest.Worksheets("Sheet1")
Set wsSource = wbSource.Worksheets("Sheet1")

With wsSource
    'Find how many records we have to deal with
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   
    'Loop over all the cells
    For recCount = 2 To lastRow
        'Get value we want to find. Change this column if needed
        strID = .Cells(recCount, "A").Value
       
        'Find item. Change address as needed
        fRow = wsDest.Range("A:A").Find(strID, , xlValues, xlWhole, , , False).Row
       
        'Copy over information. Change col IDs as needed
        wsDest.Cells(fRow, "B").Value = .Cells(recCount, "B").Value
        wsDest.Cells(fRow, "C").Value = .Cells(recCount, "C").Value
        wsDest.Cells(fRow, "D").Value = .Cells(recCount, "D").Value
        wsDest.Cells(fRow, "E").Value = .Cells(recCount, "E").Value
        '....Repeat as necessary
       
    Next recCount
End With

'Close the workbook, don't need to save
wbSource.Close False

Application.ScreenUpdating = True
       
End Sub

You can see that code opens the workbooks, defines where to look at, and then proceeds to loop over the cells. It then copies the values. You'll need to add additional lines of code for each cell value you want transferred, but you can see the pattern.
 
Luke M said:
Hi Tom,

That's enough to get the basic macro. You can probably take it from here.
Code: 
Sub TransferData()
Dim wbDest As Workbook
Dim wbSource As Workbook
 
Dim wsDest As Worksheet
Dim wsSource As Worksheet
 
Dim fRow As Long
Dim lastRow As Long
Dim recCount As Long
 
Dim strID As String
 
Application.ScreenUpdating = False
 
'Define where our workbooks are. This assumes that this macro exists in the file where info
'is GOING TO
Set wbDest = ThisWorkbook
 
'Change file path as appropriate
Set wbSource = Workbooks.Open("C:\My Documents\Change Control Form.xlsx")
 
'Which worksheets are we dealing with?
Set wsDest = wbDest.Worksheets("Sheet1")
Set wsSource = wbSource.Worksheets("Sheet1")
 
With wsSource
    'Find how many records we have to deal with
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
    'Loop over all the cells
    For recCount = 2 To lastRow
        'Get value we want to find. Change this column if needed
        strID = .Cells(recCount, "A").Value
      
        'Find item. Change address as needed
        fRow = wsDest.Range("A:A").Find(strID, , xlValues, xlWhole, , , False).Row
      
        'Copy over information. Change col IDs as needed
        wsDest.Cells(fRow, "B").Value = .Cells(recCount, "B").Value
        wsDest.Cells(fRow, "C").Value = .Cells(recCount, "C").Value
        wsDest.Cells(fRow, "D").Value = .Cells(recCount, "D").Value
        wsDest.Cells(fRow, "E").Value = .Cells(recCount, "E").Value
        '....Repeat as necessary
      
    Next recCount
End With
 
'Close the workbook, don't need to save
wbSource.Close False
 
Application.ScreenUpdating = True
      
End Sub

You can see that code opens the workbooks, defines where to look at, and then proceeds to loop over the cells. It then copies the values. You'll need to add additional lines of code for each cell value you want transferred, but you can see the pattern.
Click to expand...

Thank you so much for this Luke, I wil have a crack at it this morning.

I will let you know how I get on!
 
Luke M said:
Hi Tom,

That's enough to get the basic macro. You can probably take it from here.
Code: 
Sub TransferData()
Dim wbDest As Workbook
Dim wbSource As Workbook
 
Dim wsDest As Worksheet
Dim wsSource As Worksheet
 
Dim fRow As Long
Dim lastRow As Long
Dim recCount As Long
 
Dim strID As String
 
Application.ScreenUpdating = False
 
'Define where our workbooks are. This assumes that this macro exists in the file where info
'is GOING TO
Set wbDest = ThisWorkbook
 
'Change file path as appropriate
Set wbSource = Workbooks.Open("C:\My Documents\Change Control Form.xlsx")
 
'Which worksheets are we dealing with?
Set wsDest = wbDest.Worksheets("Sheet1")
Set wsSource = wbSource.Worksheets("Sheet1")
 
With wsSource
    'Find how many records we have to deal with
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
    'Loop over all the cells
    For recCount = 2 To lastRow
        'Get value we want to find. Change this column if needed
        strID = .Cells(recCount, "A").Value
      
        'Find item. Change address as needed
        fRow = wsDest.Range("A:A").Find(strID, , xlValues, xlWhole, , , False).Row
      
        'Copy over information. Change col IDs as needed
        wsDest.Cells(fRow, "B").Value = .Cells(recCount, "B").Value
        wsDest.Cells(fRow, "C").Value = .Cells(recCount, "C").Value
        wsDest.Cells(fRow, "D").Value = .Cells(recCount, "D").Value
        wsDest.Cells(fRow, "E").Value = .Cells(recCount, "E").Value
        '....Repeat as necessary
      
    Next recCount
End With
 
'Close the workbook, don't need to save
wbSource.Close False
 
Application.ScreenUpdating = True
      
End Sub

You can see that code opens the workbooks, defines where to look at, and then proceeds to loop over the cells. It then copies the values. You'll need to add additional lines of code for each cell value you want transferred, but you can see the pattern.
Click to expand...

Hey Luke,

I have got it working with a smaller testing Excel Workbook but when I try it within the main workbook I get Run Time Error 91 saying that the variables aren't set?

I am trying to figure out why this would happen as I have a matching ID in both Worksheets?

Could it be that my Source Workbook has Macros but when it opens via the code above the macros arent enabled?

Thanks again!
 
Thanks. Previously, code automatically assumes it finds a value. We can change the code a bit to check first, to help prevent a crash. see below
Code: 
Sub TransferData()
Dim wbDest As Workbook
Dim wbSource As Workbook

Dim wsDest As Worksheet
Dim wsSource As Worksheet

Dim fRow As Long
Dim lastRow As Long
Dim recCount As Long
Dim fCell As Range
Dim strID As String

Application.ScreenUpdating = False

'Define where our workbooks are. This assumes that this macro exists in the file where info
'is GOING TO
Set wbDest = ThisWorkbook

'Change file path as appropriate
Set wbSource = Workbooks.Open("C:\My Documents\Change Control Form.xlsx")

'Which worksheets are we dealing with?
Set wsDest = wbDest.Worksheets("Sheet1")
Set wsSource = wbSource.Worksheets("Sheet1")

With wsSource
    'Find how many records we have to deal with
   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   
    'Loop over all the cells
   For recCount = 2 To lastRow
        'Get value we want to find. Change this column if needed
        strID = .Cells(recCount, "A").Value
       
       
        'CHANGE IN CODE
        'Verify that we got a match
        'First, clear the variable
        Set fCell = Nothing
        'Then, try to set to the cell with value
        Set fCell = wsDest.Range("A:A").Find(strID, , xlValues, xlWhole, , , False)
        'Check it
        If fCell Is Nothing Then
            MsgBox "Could not find " & strID, vbOKOnly, "Not found"
            GoTo skipRow
        Else
            fRow = fCell.Row
        End If
       
       
        'Copy over information. Change col IDs as needed
        wsDest.Cells(fRow, "B").Value = .Cells(recCount, "B").Value
        wsDest.Cells(fRow, "C").Value = .Cells(recCount, "C").Value
        wsDest.Cells(fRow, "D").Value = .Cells(recCount, "D").Value
        wsDest.Cells(fRow, "E").Value = .Cells(recCount, "E").Value
        '....Repeat as necessary
       
        'MAKE SURE YOU ADD THIS
skipRow:
    Next recCount
End With

'Close the workbook, don't need to save
wbSource.Close False

Application.ScreenUpdating = True
       
End Sub

I changed out the fRow section. Since you'll have to modify what you have, don't forget to copy over the "skipRow" label, just before the Next recCount.
 
Luke M said:
Thanks. Previously, code automatically assumes it finds a value. We can change the code a bit to check first, to help prevent a crash. see below
Code: 
Sub TransferData()
Dim wbDest As Workbook
Dim wbSource As Workbook
 
Dim wsDest As Worksheet
Dim wsSource As Worksheet
 
Dim fRow As Long
Dim lastRow As Long
Dim recCount As Long
Dim fCell As Range
Dim strID As String
 
Application.ScreenUpdating = False
 
'Define where our workbooks are. This assumes that this macro exists in the file where info
'is GOING TO
Set wbDest = ThisWorkbook
 
'Change file path as appropriate
Set wbSource = Workbooks.Open("C:\My Documents\Change Control Form.xlsx")
 
'Which worksheets are we dealing with?
Set wsDest = wbDest.Worksheets("Sheet1")
Set wsSource = wbSource.Worksheets("Sheet1")
 
With wsSource
    'Find how many records we have to deal with
   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
    'Loop over all the cells
   For recCount = 2 To lastRow
        'Get value we want to find. Change this column if needed
        strID = .Cells(recCount, "A").Value
      
      
        'CHANGE IN CODE
        'Verify that we got a match
        'First, clear the variable
        Set fCell = Nothing
        'Then, try to set to the cell with value
        Set fCell = wsDest.Range("A:A").Find(strID, , xlValues, xlWhole, , , False)
        'Check it
        If fCell Is Nothing Then
            MsgBox "Could not find " & strID, vbOKOnly, "Not found"
            GoTo skipRow
        Else
            fRow = fCell.Row
        End If
      
      
        'Copy over information. Change col IDs as needed
        wsDest.Cells(fRow, "B").Value = .Cells(recCount, "B").Value
        wsDest.Cells(fRow, "C").Value = .Cells(recCount, "C").Value
        wsDest.Cells(fRow, "D").Value = .Cells(recCount, "D").Value
        wsDest.Cells(fRow, "E").Value = .Cells(recCount, "E").Value
        '....Repeat as necessary
      
        'MAKE SURE YOU ADD THIS
skipRow:
    Next recCount
End With
 
'Close the workbook, don't need to save
wbSource.Close False
 
Application.ScreenUpdating = True
      
End Sub

I changed out the fRow section. Since you'll have to modify what you have, don't forget to copy over the "skipRow" label, just before the Next recCount.
Click to expand...

That has worked perfectly!

Thank you so much for all your help and your indepth notes to help me understand it more.

This has taught me so much, I cannot thank you enough!
 
Luke M said:
Thanks. Previously, code automatically assumes it finds a value. We can change the code a bit to check first, to help prevent a crash. see below
Code: 
Sub TransferData()
Dim wbDest As Workbook
Dim wbSource As Workbook
 
Dim wsDest As Worksheet
Dim wsSource As Worksheet
 
Dim fRow As Long
Dim lastRow As Long
Dim recCount As Long
Dim fCell As Range
Dim strID As String
 
Application.ScreenUpdating = False
 
'Define where our workbooks are. This assumes that this macro exists in the file where info
'is GOING TO
Set wbDest = ThisWorkbook
 
'Change file path as appropriate
Set wbSource = Workbooks.Open("C:\My Documents\Change Control Form.xlsx")
 
'Which worksheets are we dealing with?
Set wsDest = wbDest.Worksheets("Sheet1")
Set wsSource = wbSource.Worksheets("Sheet1")
 
With wsSource
    'Find how many records we have to deal with
   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
    'Loop over all the cells
   For recCount = 2 To lastRow
        'Get value we want to find. Change this column if needed
        strID = .Cells(recCount, "A").Value
      
      
        'CHANGE IN CODE
        'Verify that we got a match
        'First, clear the variable
        Set fCell = Nothing
        'Then, try to set to the cell with value
        Set fCell = wsDest.Range("A:A").Find(strID, , xlValues, xlWhole, , , False)
        'Check it
        If fCell Is Nothing Then
            MsgBox "Could not find " & strID, vbOKOnly, "Not found"
            GoTo skipRow
        Else
            fRow = fCell.Row
        End If
      
      
        'Copy over information. Change col IDs as needed
        wsDest.Cells(fRow, "B").Value = .Cells(recCount, "B").Value
        wsDest.Cells(fRow, "C").Value = .Cells(recCount, "C").Value
        wsDest.Cells(fRow, "D").Value = .Cells(recCount, "D").Value
        wsDest.Cells(fRow, "E").Value = .Cells(recCount, "E").Value
        '....Repeat as necessary
      
        'MAKE SURE YOU ADD THIS
skipRow:
    Next recCount
End With
 
'Close the workbook, don't need to save
wbSource.Close False
 
Application.ScreenUpdating = True
      
End Sub

I changed out the fRow section. Since you'll have to modify what you have, don't forget to copy over the "skipRow" label, just before the Next recCount.
Click to expand...

Just on a side note, is there a way via VBA code to add a number to an exisiting formula but keep the audit trail of the formula still (so not use the Special Paste function).

For instance:
A1 has the formula = 10 + 10 + -20 +

And I want to add another number to it, say 30, and then add another + ready for the next number. So A1 would now look like:

= 10 + 10 + -20 + 30 +

Thank you in advance!
 
XL won't let you create a formula, manually or via macro, that ends in a mathematical operation. It's just bad syntax. But, we can do something similar. Here's a short example to give you some ideas. Uses your idea that there's a formula already in A1.
Code: 
Sub ExampleAdd()
Dim lngInput As Long
Dim myCell As Range

lngInput = InputBox("What number do you want to add?", "Add")
Set myCell = Range("A1")

'Note that we grab the Formula, not Value
myCell.Formula = myCell.Formula & "+" & lngInput
End Sub

IMO, from an audit standpoint, it'd be better to have all the number inputs go in separate cells somewhere as a static value, and then just have your formula do a
=SUM(SomeRange)
that way it's easier to see the inputs w/o having to select the actual formula.
 
Luke M said:
XL won't let you create a formula, manually or via macro, that ends in a mathematical operation. It's just bad syntax. But, we can do something similar. Here's a short example to give you some ideas. Uses your idea that there's a formula already in A1.
Code: 
Sub ExampleAdd()
Dim lngInput As Long
Dim myCell As Range
 
lngInput = InputBox("What number do you want to add?", "Add")
Set myCell = Range("A1")
 
'Note that we grab the Formula, not Value
myCell.Formula = myCell.Formula & "+" & lngInput
End Sub

IMO, from an audit standpoint, it'd be better to have all the number inputs go in separate cells somewhere as a static value, and then just have your formula do a
=SUM(SomeRange)
that way it's easier to see the inputs w/o having to select the actual formula.
Click to expand...

That makes sense, thank you again Luke.

I like your suggestion of putting the number inputs in a separate cells from an auditing standpoint. It is just a case of making a new worksheet that autopopulates the new numbers everytime I run the first macro you made.

Thank you again for all our help, you truly are the Excel Ninja!!
 
You must log in or register to reply here.
Back
Top