Macro to Copy specific columns from one workbook to another based on a condition

0

Hi,

I am trying to create a macro to copy specific columns from one sheet to another based on certain condition.
I have a cell in "Sheet 1" of Workbook "Import Temp" ,where I enter the date.If I run the macro it will look for that date in the dates column in "HS" in the workbook "HS Temp" and whenever the date matches it  will copy and paste specific columns from that  date row  into another  workbook.

I have tried the following:

Sub MoveData()

For Each cell In Worksheets("HS").Range("B3:B50") 
If cell.Value = "9/17/2018" Then   
    Range("B4:L50").Select 
    Selection.Copy ' Copy it
    Sheets("Sheet 1").Select 
    Range("A1").Select 
    ActiveSheet.Paste
    Sheets("HS").Select 
    Range("A1").Select
    Application.CutCopyMode = False 
End If
Next ' Move forward to the next

End Sub

This code works ,but it just copies the data to another sheet of the same workbook.

PLEASE HELP.

Answer
Discuss

Answers

0

Do a search on here for this topic and you will find a lot of results. But, basically, you do something like this:

' Reference another workbook
Set Wb1 = Workbooks.Open("C:\sample.xls")

' Paste the data
Wb1.Sheets("Sheet Name").Range("A1").Paste

Here are some helpful links:

TeachExcel Resources

Macro to get Data from Another Workbook in Excel

Discuss
0

No sir. Your code doesn't work. Here is what it does.

  1. It looks at every cell in ther range B3:B50
  2. If that  cell has the value "9/17/2018"
    it copies the range B4:L50 to the clipboard
  3. It then pastes that range - all 46 rows of it to another sheet
    starting at A1 of that sheet.
  4. It will do that 47 times (once for each cell in B3:B50) if the date is found there

Obviously, only once should be quite enough. You can achieve that by adding the code Exit For as the last row within the If ... End If section. Your code would then look, in turn, at every cell in B3:B50 and if one of them has the specified date in it B4:L50 would be copied to the other sheet. - Probably not very logical but, at least, not repetitive. Perhaps you mean to copy cells only from one row. That would be the subject of another question: how to specify a range address with variable rows.

At the level at which you are now programming you should try to understand objects. Excel's most important object is the Selection object. It is the range you have selected. It can be one cell or many and it needs user action. VBA, however, is designed to act on the user's behalf. The simple fact is that, since the Selection object is always a range, VBA can deal directly with the range - the Range object - without the need of creating a Selection object. Here is a demonstration of the effect.

            ' assigns the Range object "B4:L50" to a new Selection object
            Range("B4:L50").Select
            ' copies the Selection object instead of the Range object
            Selection.Copy

            ' The above is equivalent to
            Range("B4:L50").Copy

Other objects are the Workbook and the Worksheet objects. The macro recorder uses the same roundabout way to translate your keystrokes into functioning code. That's because the recorder can't know what you have in mind until after you press a key. You don't have a similar disadvantage when you write code yourself.

            ' Here the Select method is used to create an ActiveSheet
            '   (Why not use the Activate method to be more precise?
            '    Sheets("Sheet 1").Activate would be clearer.)
            Sheets("Sheet 1").Select

            ' Then the ActiveSheet is used to paste to
            ActiveSheet.Paste


            ' Logically, the above is equivalent to
            Sheets("Sheet 1").Paste

Unfortunately, the above logical construct doesn't work in this case because the Paste command needs an ActiveCell. The ActiveCell is always a part of the Selection object. Your code creates a Selection object comprising of only the ActiveCell with Range("A1").Select.

VBA provides a one-liner to do the entire job by avoiding both the (two) Selection objects, the (two) ActiveSheet objects and the ActiveCell object.

Worksheets("HS").Range("B4:B50").Copy Destination:=Worksheets("Sheet 1").Range("A1")

Observe that the ActiveSheet is still the sheet which was active before the code ran, the Selection is still the selection the user made before the code ran and no cell was activated. In a nutshell, VBA doesn't interfere in the user's work.

Best practise suggests that you declare the workbooks and worksheets you intend to use as variables. Instead of activating this sheet or that, as you would do when clicking with the mouse, you assign each workbook to a variable.

    Dim WbSource As Workbook, WbTarget As Workbook
    Dim WsSource As Worksheet, WsTarget As Worksheet

    Set WbSource = Workbooks("My Data Source.xlsx")     ' must be open
    Set WbTarget = ThisWorkbook

    Set WsSource = WbSource.Worksheets("HS")
    Set WsTarget = TbTarget.Worksheets("Sheet 1")

You might simplify the above by avoiding the explicit declaration of the Target workbook.

    Dim WbSource As Workbook
    Dim WsSource As Worksheet, WsTarget As Worksheet

    Set WbSource = Workbooks("My Data Source.xlsx")     ' must be open

    Set WsSource = WbSource.Worksheets("HS")
    Set WsTarget = ThisWorkbook.Worksheets("Sheet 1")

Either way, you can now write your code much easier than was possible while you "saved" all the time spent on identifying your objects.

    Dim Cell As Range

    For Each Cell In WsSource.Range("B3:B50")
        If Cell.Value = "9/17/2018" Then
            WsSource.Range("B4:L50").Copy Destination:=WsTarget.Cells(1, "A")
            ' Destination:=WsTarget.Range("A1") also works but looks odd in VBA
           ' I would use Destination:=WsTarget.Cells(1, 1)
            Exit For
        End If
    Next Cell          ' Move forward to the next cell
Discuss

Discussion

There are issues in this code too.

I will be more specific.

I have file named 'HS' and I have file named 'Import'.
I want to copy data from 'Sheet 1' of 'HS' TO 'Sheet 1' of 'Import'.

Now suppose I have the Macro button in 'Sheet 2' of 'Import'.
When I click on the Macro button it shoud look up for the date in any cell in 'Sheet 2' of 'Import'.
It should match this date with the date column in 'HS' 'Sheet 1' and copy data only for this DATE to 'Sheet 1' of 'Import' starting from the 2nd row.(The first row will be headers)

WHAT DATA TO COPY:
From 'Sheet 1' of 'HS'                              To 'Sheet 1' of 'Import'
Column C                                                         Column G
Column D                                                         Column A
Column E                                                         Column B
Column G                                                         Column C

Now both the workbooks will be open.It just has to copy the data based on the criteria.

PLEASE HELP!
   
 
Roy Aug 20, '19 at 1:58 pm
You have received a lengthy, free lesson on writing VBA code. The best way you might benefit from it would be to write VBA code, implementing what you have learned. Then, if there are "issues" with the code you wrote, ask a question.
Variatus (rep: 3233) Aug 21, '19 at 12:08 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login