Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro copy with criteria
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default Macro copy with criteria
Hi, can this macro can be modified to copy from range A6:G100 rows that has
in G column today's date?(18.09.2009)

Sub COPY()
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Workbooks.Open Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" &
MyWorkbook & ".xls"
Range("A5:T200").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
End Sub

Can this be done?
Thanks!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default Macro copy with criteria
this code will work except I'm not sure how the dates are store in column g.
I used autofilter and the format of the filtered data in the autofilter may
need to be changed for the code to work. In the us I founc the date and
month are not two digits so you may have to modify the filter to look like
this

from
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
to
MyWorkbook = "" & Format(Date, "d.m.yyyy")


You will need a different formated date for opening the workbook and the
date used in the autofilter.

I also modifiy the code to specify the worksheets rather than to relie on
the active sheet.


Sub COPY()

MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" & _
MyWorkbook & ".xls")
Set bk = ThisWorkbook
With bk.Sheets(1)
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=MyWorkbook
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).COPY
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

End With
End Sub



"puiuluipui" wrote:

Hi, can this macro can be modified to copy from range A6:G100 rows that has
in G column today's date?(18.09.2009)

Sub COPY()
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Workbooks.Open Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" &
MyWorkbook & ".xls"
Range("A5:T200").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
End Sub

Can this be done?
Thanks!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default Macro copy with criteria
Hi Joel. I've made a misstake. This is the code i need to be changed:

Sub copyanul()
Workbooks.Open Filename:="C:\Users\puiut\Desktop\login
password\apollo\Comenzi ANULATE 2009.xls"
Range("B3").Select
Range("A8:G250").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:H").EntireColumn.AutoFit
Range("E:E,H:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("B3").Select
Application.CutCopyMode = False
Windows("Comenzi ANULATE 2009.xls").Close
End Sub


I tried to adjust your code to this code, but i can't do it. Can you make
this code copy only rows, from A8:G250 range, with today date in G column?
The same thing like before, but with this code.
Sorry again.
Thanks!

"Joel" wrote:

this code will work except I'm not sure how the dates are store in column g.
I used autofilter and the format of the filtered data in the autofilter may
need to be changed for the code to work. In the us I founc the date and
month are not two digits so you may have to modify the filter to look like
this

from
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
to
MyWorkbook = "" & Format(Date, "d.m.yyyy")


You will need a different formated date for opening the workbook and the
date used in the autofilter.

I also modifiy the code to specify the worksheets rather than to relie on
the active sheet.


Sub COPY()

MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" & _
MyWorkbook & ".xls")
Set bk = ThisWorkbook
With bk.Sheets(1)
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=MyWorkbook
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).COPY
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

End With
End Sub



"puiuluipui" wrote:

Hi, can this macro can be modified to copy from range A6:G100 rows that has
in G column today's date?(18.09.2009)

Sub COPY()
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Workbooks.Open Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" &
MyWorkbook & ".xls"
Range("A5:T200").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
End Sub

Can this be done?
Thanks!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default Macro copy with criteria
Try these changes

Sub COPY()

DateStr = Format(Date, "m/d/yyyy")

Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\login password\" & _
apollo\Comenzi ANULATE 2009.xls"

With bk.Sheets(1)
.Range("G").NumberFormat = fiormat(Date,"m/d/yyyy"
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=DateStr
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).Copy
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

.Columns("B:H").AutoFit
.Range("E:E,H:H").NumberFormat = "m/d/yyyy"
.Range("B3").Select

End With

bk.Close savechanges:=false

End Sub


"puiuluipui" wrote:

Hi Joel. I've made a misstake. This is the code i need to be changed:

Sub copyanul()
Workbooks.Open Filename:="C:\Users\puiut\Desktop\login
password\apollo\Comenzi ANULATE 2009.xls"
Range("B3").Select
Range("A8:G250").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:H").EntireColumn.AutoFit
Range("E:E,H:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("B3").Select
Application.CutCopyMode = False
Windows("Comenzi ANULATE 2009.xls").Close
End Sub


I tried to adjust your code to this code, but i can't do it. Can you make
this code copy only rows, from A8:G250 range, with today date in G column?
The same thing like before, but with this code.
Sorry again.
Thanks!

"Joel" wrote:

this code will work except I'm not sure how the dates are store in column g.
I used autofilter and the format of the filtered data in the autofilter may
need to be changed for the code to work. In the us I founc the date and
month are not two digits so you may have to modify the filter to look like
this

from
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
to
MyWorkbook = "" & Format(Date, "d.m.yyyy")


You will need a different formated date for opening the workbook and the
date used in the autofilter.

I also modifiy the code to specify the worksheets rather than to relie on
the active sheet.


Sub COPY()

MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" & _
MyWorkbook & ".xls")
Set bk = ThisWorkbook
With bk.Sheets(1)
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=MyWorkbook
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).COPY
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

End With
End Sub



"puiuluipui" wrote:

Hi, can this macro can be modified to copy from range A6:G100 rows that has
in G column today's date?(18.09.2009)

Sub COPY()
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Workbooks.Open Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" &
MyWorkbook & ".xls"
Range("A5:T200").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
End Sub

Can this be done?
Thanks!
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default Macro copy with criteria
Hi Joel, i have some errors.
First i had errors with:
Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\login password\" & _
apollo\Comenzi ANULATE 2009.xls"
And i made it like this:
Set bk = Workbooks.Open(Filename:="C:\Users\puiut\Desktop\l ogin
password\apollo\Comenzi ANULATE 2009.xls")
And now i have problem with this:
.Range("G").NumberFormat = Format(Date, "dd.mm.yyyy"
This line is highlited with yellow.
What am i doing wrong?
Thanks!




"Joel" wrote:

Try these changes

Sub COPY()

DateStr = Format(Date, "m/d/yyyy")

Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\login password\" & _
apollo\Comenzi ANULATE 2009.xls"

With bk.Sheets(1)
.Range("G").NumberFormat = fiormat(Date,"m/d/yyyy"
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=DateStr
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).Copy
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

.Columns("B:H").AutoFit
.Range("E:E,H:H").NumberFormat = "m/d/yyyy"
.Range("B3").Select

End With

bk.Close savechanges:=false

End Sub


"puiuluipui" wrote:

Hi Joel. I've made a misstake. This is the code i need to be changed:

Sub copyanul()
Workbooks.Open Filename:="C:\Users\puiut\Desktop\login
password\apollo\Comenzi ANULATE 2009.xls"
Range("B3").Select
Range("A8:G250").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:H").EntireColumn.AutoFit
Range("E:E,H:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("B3").Select
Application.CutCopyMode = False
Windows("Comenzi ANULATE 2009.xls").Close
End Sub


I tried to adjust your code to this code, but i can't do it. Can you make
this code copy only rows, from A8:G250 range, with today date in G column?
The same thing like before, but with this code.
Sorry again.
Thanks!

"Joel" wrote:

this code will work except I'm not sure how the dates are store in column g.
I used autofilter and the format of the filtered data in the autofilter may
need to be changed for the code to work. In the us I founc the date and
month are not two digits so you may have to modify the filter to look like
this

from
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
to
MyWorkbook = "" & Format(Date, "d.m.yyyy")


You will need a different formated date for opening the workbook and the
date used in the autofilter.

I also modifiy the code to specify the worksheets rather than to relie on
the active sheet.


Sub COPY()

MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" & _
MyWorkbook & ".xls")
Set bk = ThisWorkbook
With bk.Sheets(1)
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=MyWorkbook
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).COPY
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

End With
End Sub



"puiuluipui" wrote:

Hi, can this macro can be modified to copy from range A6:G100 rows that has
in G column today's date?(18.09.2009)

Sub COPY()
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Workbooks.Open Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" &
MyWorkbook & ".xls"
Range("A5:T200").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
End Sub

Can this be done?
Thanks!
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default Macro copy with criteria
.Columns("G").NumberFormat = format(Date,"m/d/yyyy")


"puiuluipui" wrote:

Hi Joel, i have some errors.
First i had errors with:
Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\login password\" & _
apollo\Comenzi ANULATE 2009.xls"
And i made it like this:
Set bk = Workbooks.Open(Filename:="C:\Users\puiut\Desktop\l ogin
password\apollo\Comenzi ANULATE 2009.xls")
And now i have problem with this:
.Range("G").NumberFormat = Format(Date, "dd.mm.yyyy"
This line is highlited with yellow.
What am i doing wrong?
Thanks!




"Joel" wrote:

Try these changes

Sub COPY()

DateStr = Format(Date, "m/d/yyyy")

Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\login password\" & _
apollo\Comenzi ANULATE 2009.xls"

With bk.Sheets(1)
.Range("G").NumberFormat = fiormat(Date,"m/d/yyyy"
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=DateStr
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).Copy
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

.Columns("B:H").AutoFit
.Range("E:E,H:H").NumberFormat = "m/d/yyyy"
.Range("B3").Select

End With

bk.Close savechanges:=false

End Sub


"puiuluipui" wrote:

Hi Joel. I've made a misstake. This is the code i need to be changed:

Sub copyanul()
Workbooks.Open Filename:="C:\Users\puiut\Desktop\login
password\apollo\Comenzi ANULATE 2009.xls"
Range("B3").Select
Range("A8:G250").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:H").EntireColumn.AutoFit
Range("E:E,H:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("B3").Select
Application.CutCopyMode = False
Windows("Comenzi ANULATE 2009.xls").Close
End Sub


I tried to adjust your code to this code, but i can't do it. Can you make
this code copy only rows, from A8:G250 range, with today date in G column?
The same thing like before, but with this code.
Sorry again.
Thanks!

"Joel" wrote:

this code will work except I'm not sure how the dates are store in column g.
I used autofilter and the format of the filtered data in the autofilter may
need to be changed for the code to work. In the us I founc the date and
month are not two digits so you may have to modify the filter to look like
this

from
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
to
MyWorkbook = "" & Format(Date, "d.m.yyyy")


You will need a different formated date for opening the workbook and the
date used in the autofilter.

I also modifiy the code to specify the worksheets rather than to relie on
the active sheet.


Sub COPY()

MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Set bk = Workbooks.Open( _
Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" & _
MyWorkbook & ".xls")
Set bk = ThisWorkbook
With bk.Sheets(1)
LastRow = .Range("G" & Rows.Count).End(xlUp).Row
.Columns("G").AutoFilter
.Columns("G").AutoFilter Field:=1, _
Criteria1:=MyWorkbook
.Rows("2:" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).COPY
End With


With Workbooks("Database.xls").ActiveSheet
.Rows(2).PasteSpecial _
Paste:=xlPasteValues
.Range("B2").Select

End With
End Sub



"puiuluipui" wrote:

Hi, can this macro can be modified to copy from range A6:G100 rows that has
in G column today's date?(18.09.2009)

Sub COPY()
MyWorkbook = "" & Format(Date, "dd.mm.yyyy")
Workbooks.Open Filename:="C:\Users\puiut\Desktop\try copy add\Copy Luke\" &
MyWorkbook & ".xls"
Range("A5:T200").Select
Selection.COPY
Windows("Database.xls").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
End Sub

Can this be done?
Thanks!
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
macro criteria copy puiuluipui Excel Discussion (Misc queries) 9 December 1st 08 12:53 PM
Macro - How to not copy blank criteria? NPell Excel Worksheet Functions 1 September 6th 08 07:53 PM
copy data with criteria linda Excel Discussion (Misc queries) 4 September 10th 07 09:22 AM
under certain criteria copy data. dave Excel Worksheet Functions 2 November 16th 04 02:56 AM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement