VBA Copy data to another workbook based on criteria

A

atame

New Member
Joined
May 26, 2015
Messages
31
Hi All,

I have a workbook that i would like to copy some data from, to another workbook. I need it to find all values greater than £0.01 in column L the copy the data from column B and L only to Workbook 2, sheet 1. Starting from A9. Everytime the button is clicked it need to clear all the data in workbook 2 below row 8, then paste the new data in. Once the data has been pasted, then save and close workbook 2. It will need to loop though the data as quantitys of data can change each month.

Workbook1, sheet "MergedData") contains data to be copied.

Workbook2, Sheet1, is where the data need to be copied, starting a A9.

I don't have any code so far, so am staring from scratch.

Any help will be greatly appreciated!!!

Thanks
Aarron
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hello,

This code can be made simpler, but does it work as expected?

Code: 
Sub COPY_FROM_MERGEDDATA()
    Application.ScreenUpdating = False
    With Workbooks("Workbook2")
        Sheets("Sheet1").Rows("9:" & Rows.Count).ClearContents
    End With
    Workbooks("Workbook1").Activate
    Sheets("MergedData").Select
    Sheets("MergedData").Range("L1").Select
    Selection.AutoFilter Field:=12, Criteria1:=">0.01", Operator:=xlAnd
    Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("A9").PasteSpecial (xlPasteValues)
    Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("B9").PasteSpecial (xlPasteValues)
    Selection.AutoFilter
    Workbooks("Workbook2").Close (1)
    Application.ScreenUpdating = True
End Sub

It does assume both workbooks are open.
 
Last edited:
Upvote 0
onlyadrafter said:
Hello,

This code can be made simpler, but does it work as expected?

Code: 
Sub COPY_FROM_MERGEDDATA()
    Application.ScreenUpdating = False
    With Workbooks("Workbook2")
        Sheets("Sheet1").Rows("9:" & Rows.Count).ClearContents
    End With
    Workbooks("Workbook1").Activate
    Sheets("MergedData").Select
    Sheets("MergedData").Range("L1").Select
    Selection.AutoFilter Field:=12, Criteria1:=">0.01", Operator:=xlAnd
    Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("A9").PasteSpecial (xlPasteValues)
    Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("B9").PasteSpecial (xlPasteValues)
    Selection.AutoFilter
    Workbooks("Workbook2").Close (1)
    Application.ScreenUpdating = True
End Sub

It does assume both workbooks are open.
Click to expand...

Hi Mate,

Thanks for getting its over to me!

I have replaced the folowing code to open the document fist
Code: 
    Application.ScreenUpdating = False    With Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls")
        Sheets("Sheet1").Rows("9:" & Rows.Count).ClearContents
    End With

But i get a Run-time error '13': Type Mismatch. Can you help with this??

Thanks
 
Upvote 0
Hi,

i haven't been able to get you code to work.

I have come up with so far, which opens the workbook, and clears the data (this was the only way i could get it to work.)

Code: 
Dim wb1 As WorkbookDim wb2 As Workbook
    Application.ScreenUpdating = False
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls")
    
    With wb2
        Worksheets("Sheet1").Range("A9:B10000").ClearContents
    End With

I then have this code that can copy the data if the value matches the criteria, (only works for same workbook i.e Sheet to Sheet) cannot get it to copy to another workbook.
Code: 
Dim LR As Long, i As LongWith Sheets("MergedData")
    LR = .Range("L" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("L" & i)
        If .Value > 0 Then
        .Offset(, -10).Copy Destination:=Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Copy Destination:=Sheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Offset(1)
        End If
        End With
    Next i
End With

I would really appreciate all help provided.
 
Upvote 0
Hello,

is this any better?

Code: 
Sub COPY_FROM_MERGEDDATA()
    Workbooks.Open ("C:\Workbook2.xls")
    Application.ScreenUpdating = False
    With Workbooks("Workbook2")
        Sheets("Sheet1").Rows("9:" & Rows.Count).ClearContents
    End With
    Workbooks("Workbook1").Activate
    Sheets("MergedData").Select
    Sheets("MergedData").Range("L1").Select
    Selection.AutoFilter Field:=12, Criteria1:=">0.01", Operator:=xlAnd
    Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("A9").PasteSpecial (xlPasteValues)
    Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("B9").PasteSpecial (xlPasteValues)
    Selection.AutoFilter
    Workbooks("Workbook2").Close (1)
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
onlyadrafter said:
Hello,

is this any better?

Code: 
Sub COPY_FROM_MERGEDDATA()
    Workbooks.Open ("C:\Workbook2.xls")
    Application.ScreenUpdating = False
    With Workbooks("Workbook2")
        Sheets("Sheet1").Rows("9:" & Rows.Count).ClearContents
    End With
    Workbooks("Workbook1").Activate
    Sheets("MergedData").Select
    Sheets("MergedData").Range("L1").Select
    Selection.AutoFilter Field:=12, Criteria1:=">0.01", Operator:=xlAnd
    Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("A9").PasteSpecial (xlPasteValues)
    Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Workbook2").Sheets("Sheet1").Range("B9").PasteSpecial (xlPasteValues)
    Selection.AutoFilter
    Workbooks("Workbook2").Close (1)
    Application.ScreenUpdating = True
End Sub
Click to expand...
Hi Mate,

With help from others i have got this code that works. Although it copies data in reverse, so the last row is the source sheet goes to row 1 in destination sheet??

Code: 
Private Sub CommandButton2_Click()    Dim LastDRow As Integer, _
        InitWorkSheet As Worksheet, _
        DestWorkSheet As Worksheet, _
        myData As Workbook, _
        LastWRow As Integer
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .CutCopyMode = False
    End With


Set InitWorkSheet = ActiveWorkbook.Sheets("MergedData")


Set myData = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls")
DoEvents
Set DestWorkSheet = myData.Sheets("Sheet1")


With myData
            DestWorkSheet.Rows(9 & ":" & DestWorkSheet.Rows.Count).ClearContents
End With


With InitWorkSheet
    LastDRow = .Rows(.Rows.Count).End(xlUp).Row
    For i = LastDRow To 1 Step -1
        If .Cells(i, "L") < 0.01 Then
        Else
            LastWRow = DestWorkSheet.Cells(DestWorkSheet.Rows.Count, "A").End(xlUp).Row + 1
            If LastWRow < 9 Then LastWRow = 9
            DestWorkSheet.Cells(LastWRow, 1) = .Cells(i, "B")
            DestWorkSheet.Cells(LastWRow, 2) = .Cells(i, "L")
        End If
    Next i
End With


myData.Save
End Sub
 
Upvote 0
Hi atame

. you explained quite well what you want. And I expect onlyadrafter will get you there a lot quicker than me, so you will probably just want to stay with that..
....

... But I am practicing these things just now, so maybe later I could give it a go in parallel, if you still need something then..

.. I am a bit of a Pictures man, and so is this Forum!!-

. I know it is a bit of a pain as you have not been here long, but it is always helpful and preferred at MrExcel if you can give a nice “Picture” of what you have and another of what you want ...

... so, if you did want me to have a go..


Try and prepare a clear “Before” and “After” “Picture”


. To do that you can either
. 1) - post two screenshots ( see notes for how to do that in my signature –Please do not post am image as we cannot copy that to a spreadsheet!)
. or
. 2) - Up left in the Thread editor is a Table icon. Click that, create appropriately sized tables and fill them in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)

.3 ) – attach a file or files, with reduced data, made up data if you like, and fill in your Workbook 2, sheet 1 by hand showing exactly how you want it finally to look. . For example send over this free thing: : Box Net,,
Remember to select Share after uploading and give us the link they provide.
………..
. Make sure there is just enough data to make it clear what is needed. Remember to desensitize the data if necessary– make the data up if you like, as long as it is representative of all possible scenarios and data types.
. - So the “Before” should have just your initial data.
. - The After, (Workbook 2, sheet 1 ) hand written by you should show exactly how it should look as a result of a any code based on the exact sample data you give in the existing sheet example


Alan

P.s. It would be easier if the workbooks were both open before running any code, which I think maybe
Onlyadrafter may have assumed
 
Upvote 0
DocAElstein said:
Hi atame

. you explained quite well what you want. And I expect onlyadrafter will get you there a lot quicker than me, so you will probably just want to stay with that..
....

... But I am practicing these things just now, so maybe later I could give it a go in parallel, if you still need something then..

.. I am a bit of a Pictures man, and so is this Forum!!-

. I know it is a bit of a pain as you have not been here long, but it is always helpful and preferred at MrExcel if you can give a nice “Picture” of what you have and another of what you want ...

... so, if you did want me to have a go..


Try and prepare a clear “Before” and “After” “Picture”


. To do that you can either
. 1) - post two screenshots ( see notes for how to do that in my signature –Please do not post am image as we cannot copy that to a spreadsheet!)
. or
. 2) - Up left in the Thread editor is a Table icon. Click that, create appropriately sized tables and fill them in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)

.3 ) – attach a file or files, with reduced data, made up data if you like, and fill in your Workbook 2, sheet 1 by hand showing exactly how you want it finally to look. . For example send over this free thing: : Box Net,,
Remember to select Share after uploading and give us the link they provide.
………..
. Make sure there is just enough data to make it clear what is needed. Remember to desensitize the data if necessary– make the data up if you like, as long as it is representative of all possible scenarios and data types.
. - So the “Before” should have just your initial data.
. - The After, (Workbook 2, sheet 1 ) hand written by you should show exactly how it should look as a result of a any code based on the exact sample data you give in the existing sheet example


Alan

P.s. It would be easier if the workbooks were both open before running any code, which I think maybe
Onlyadrafter may have assumed
Click to expand...

Hi Alan,

This is the data Columns B & L (BOLD) that i am trying to copy out of workbook 1 sheet "MergedData" into Bookings.xls

5027801108721032 Fake House, Fake Road, FK1 1FK420608362083620
50278711787212 Fake House, Fake Road, FK1 1FK1301969.231969.23
5027861168725 Fake House, Fake Road, FK1 1FK91843.95843.95
5027901109723 Fake House, Fake Road, FK1 1FK45.5754.31754.31

<tbody>
</tbody>


And im trying to get it into bookings.xls starting at row 9.

Region Code:###
Accounts Ref:###
Sub Contractor Name:###
For Month Of:2015-05
Application Ref:2015-05
W/O Number Total Net
27801 £ 83,620.00
27871 £ 1,969.23
27861 £ 843.95
27901 £ 754.31

<tbody>
</tbody>

The code i have at the moment is copying the data, but it is coping in the wrong direction, example below:

Region Code:###
Accounts Ref:###
Sub Contractor Name:###
For Month Of:2015-05
Application Ref:2015-05
W/O Number Total Net
27901 £ 754.31
27861 £ 843.95
27871 £ 1,969.23
27801 £ 83,620.00

<tbody>
</tbody>


This is the code i have that works but in the wrong direction:
Code: 
Private Sub CommandButton2_Click()    
        Dim LastDRow As Integer, _
        InitWorkSheet As Worksheet, _
        DestWorkSheet As Worksheet, _
        myData As Workbook, _
        LastWRow As Integer
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .CutCopyMode = False
    End With


Set InitWorkSheet = ActiveWorkbook.Sheets("MergedData")


Set myData = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls")
DoEvents
Set DestWorkSheet = myData.Sheets("Sheet1")


With myData
            DestWorkSheet.Rows(9 & ":" & DestWorkSheet.Rows.Count).ClearContents
End With


With InitWorkSheet
    LastDRow = .Rows(.Rows.Count).End(xlUp).Row
    For i = LastDRow To 1 Step -1
        If .Cells(i, "L") < 0.01 Then
        Else
            LastWRow = DestWorkSheet.Cells(DestWorkSheet.Rows.Count, "A").End(xlUp).Row + 1
            If LastWRow < 9 Then LastWRow = 9
            DestWorkSheet.Cells(LastWRow, 1) = .Cells(i, "B")
            DestWorkSheet.Cells(LastWRow, 2) = .Cells(i, "L")
        End If
    Next i
End With


myData.Save
End Sub

Thanks for all the assistance!!!
 
Upvote 0
atame said:
......
This is the data Columns B & L (BOLD) that i am trying to copy out of workbook 1 sheet "MergedData" into Bookings.xls
.......

And im trying to get it into bookings.xls starting at row 9.

The code i have at the moment is copying the data, but it is coping in the wrong direction, example below:
.....

This is the code i have that works but in the wrong direction:
............
Click to expand...

Hi,
Thanks for getting that info so quickly across.
.
. If no one else picks the Thread up then I will take a look later, or early tomorrow

Alan

P.s.

It is allways good to reply with a quote, as you did as that helps make sure we know who you are talking to.., but chop some bits out, as I have done above with your quote ...
That helps make the Thread more readyble
 
Upvote 0
DocAElstein said:
Hi,
Thanks for getting that info so quickly across.
.
. If no one else picks the Thread up then I will take a look later, or early tomorrow
Click to expand...

Hi,

I look forward to seeing what you come up with. Thanks for the assistance.

Aarron
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
216
Insurance Dave
I
P
  • Question
Automating copying of columns within same sheet in excel using VBA.
Replies
1
Views
130
MountainFog
M
P
  • Question
Code used for comparison between 2 list.
Replies
3
Views
306
Tupe77
T
L
  • Question
Copy Subtotals from Source Workbook to Current Workbook
Replies
1
Views
249
DanteAmor
DanteAmor
T
  • Solved
Copy and Past the Data from Multiple Closed Workbooks
2
Replies
16
Views
888
thespardian
T

Forum statistics

Threads
1,214,813
Messages
6,121,705
Members
449,048
Latest member
81jamesacct

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top