ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[Âà¶K] VBAªº¼g§@§Þ¥©»P¼W¶i®Ä¯à

±q¦¹¤å³¹¾Ç¨ìºëµØ¡A³o¨Ç²¤Æ¤è¦¡¦b¦U¥ý¶i´£¨Ñ¸Ñµª¤¤
³£¥i¥H¾Ç¨ì¡A¸g¥Ñ¦¹½g§óºëµØ§l¦¬¡A·P®¦
Á`¬O§Q¥Î¥¨¶°¿ý»s¡A¦A¨Ì¾Ç²ß¨ìªºVBA»yªk
·Q·Q«ç»ò²¤Æ¡A¨Ó¨ì³o¬Ý¬Ý¨ä¥L¤Hªº¼gªk
¦A¬Ý¬Ý¥H«e¼gªº¡AÁ`¬O¥i¥H¾Ç¨ì¤@¨Ç
¹³¬O³æ¯Â²{¦s¼Æ¾Ú¡A¥H«eÁ`¬O¥Ç¤F¿ï¨úcells
¦A³B²z¡Aªñ¤é¾Ç¨ì¤@¨Ç¡A°w¹ï§Q¥Î³B²z¼Æ¾Ú
¼g¤F¤@¬q³B²z®æ¦¡ªº»yªk
¥Ø«e³B²z¸ê®Æ©|¥i¡A¦ý¤@ª½«ùÄò¾Ç²ß²¤Æ¼W¶i®Ä¯à§Þ¥©¤¤
Ãö©óApplication.ScreenUpdating ¾Ç¨ì¡A¥¿¦b¹B¥Î²¤Æ¤¤
¥Ø«e¥¿¦b¾Ç²ß¦p¦ó²¤Æ¡A¦pªG¦³Â²¤Æidea ·Ð½Ð´£¨Ñ¨Ñ¾Ç²ß¡A·P®¦

KRowEnd = Cells(Rows.Count, 1).End(xlUp).Row '¥H AÄæ¸ê®Æ¬°°ò¦ =1 §PÂ_½d³ò
kcolend = Cells(1, Columns.Count).End(xlToLeft).Column '¥H ²Ä¤@¦C¸ê®Æ¬°°ò¦ =1§PÂ_§PÂ_½d³ò

MsgBox "¦C¼ÆKRowEnd=" & KRowEnd
MsgBox "Äæ¼Ækcolend=" & kcolend

Range(Cells(1, 1), Cells(1, kcolend)).Select

    With Selection.Interior
        .ColorIndex = 36 '²L¶À¦â
        .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 3


Range(Cells(2, 1), Cells(KRowEnd, kcolend)).Select

    ActiveWindow.FreezePanes = True
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(),2)"
    Selection.FormatConditions(1).Interior.ColorIndex = 15
Range(Cells(1, 1), Cells(1, kcolend)).Select
    Selection.AutoFilter
    Cells.EntireColumn.AutoFit

TOP

専¤ß¬Ý¤F¤@¤ù,«Ü¹ê¥Î.
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

·PÁ¤j¤j¤À¨É!

TOP

¬Ý§¹¦¨¤§«á~Æ[©ÀÁÙ¬O«Ü¼Ò½k~À³¸Ó¥ý¥h¶R¥»®Ñ¨Ó¬Ý~

TOP

³Qª©¥D²q¤¤¤F,,,´N¬O,¥Î¨ººØ²Â¤è¦¡,,±o¨ìµ²ªG..

¦n¦nµ¹¥¦¾Ç°_¨Ó,ª©¥D©Ò±Ðªº...

·P®¦..

TOP

¦pªG§Ú´_»sÀx¦s®æ«á,¥u·Q¶K¤W³¡¥÷®æ¦¡,
¥Ø«eµ{¦¡¦p¤U, ½Ð°Ý³o¥i¥H¦b²¤Æ¶Ü?
©Î¬O¦³¨ä¥Lªº¼gªk

    Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteFormats
    Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteValidation

TOP

¦n¹ê»Úªº¶K¤Á°ÝÃD»¡©ú
§ÚªºÀY¸£¬ðµM¥X²{¤@½u¥ú
¯u¬O~Æg
¶}¤ß¾Ç²ß,¾Ç²ß«Ü¶}¤ß

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-10-31 22:33 ½s¿è
¦pªG§Ú´_»sÀx¦s®æ«á,¥u·Q¶K¤W³¡¥÷®æ¦¡,¥Ø«eµ{¦¡¦p¤U, ½Ð°Ý³o¥i¥H¦b²¤Æ¶Ü?
Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteFormats
    Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteValidation
wind6424 µoªí©ó 2011-10-19 15:01

1. ·í­«½Æ°Ñ·Ó¦P¤@¼Ðªºª«®É
¥iµ½¥Î With ... End With

2. ·í Copy «á­Y¶K¤W«á°¨¤W¤S­n¶K¤Wªº¸Ü,
µL¶·¦A¦¸ Copy

3. Sheet1 ¬O«O¯d¦r¤£¯àª½±µ®³¨Ó¤Þ¥Î,
·|µo¥Í¿ù»~
Set Sh9R = Sheets("Sheet9").Rows(1)
Set Sh1R = Sheets("Sheet1").Rows(1)

Sh9R.Copy
With Sh1R
  .PasteSpecial Paste:=xlPasteFormats
  .PasteSpecial Paste:=xlPasteValidation
End With

«á°O­×¥¿ :
«á¨Ó¸g¹L´ú¸Õµo²{°ÝÃDÂIÀ³¸Ó¤£¦b Sheet1 (§Y¨ä¨Ã«D«O¯d¦r),
¥¦À³¬O¥Nªí Sheets(1).
¦Ó Sheet9 µo¥Í°ÝÃDªº­ì¦]¬O §Ú´ú¸Õ®É©Ò·s«Øªº Excel Àɨä¹w³]­È³£¥u·|¶}¨ì Sheets(3),
¬G¦Ó¥Î Sheet9 ·|µo¥Í¿ù»~.

µM¦Ó¦]¬° Sheet Name ¨Ã¤£·|»P Sheet No. §¹¥þ¬Û¹ïÀ³,
©Ò¥H¸Ó Sheet1 ¨ì©³¬O¤£¬O Sheet(1) ¨S¶i VBA ¤@¯ë¬O¬Ý¤£¥X¨Óªº,
©Ò¥HÁÙ¬O«Øij¨Ï¥Î Sheets("Sheet1") §Î¦¡¦A·f°t With ¨Ó²¤Æµ{¦¡,
³o¬O­Ó¤Hªº¼gµ{¦¡²ßºD¶È¨Ñ°Ñ¦Ò.

TOP

1. ·í­«½Æ°Ñ·Ó¦P¤@¼Ðªºª«®É
¥iµ½¥Î With ... End With

2. ·í Copy «á­Y¶K¤W«á°¨¤W¤S­n¶K¤Wªº¸Ü,
µL¶· ...
luhpro µoªí©ó 2011-10-20 23:01

½Ð°Ý luhpro ¤j
¦pªG§Úªº­n¶Kªº¦a¤è¬OÅܼÆ,¬O¤£¬O¤£¯à³o¼Ë¼g
¦]¬°§Ú°õ¦æ·|¦³°ÝÃD
¥¿½Tªº¤è¦¡¬O­n¦p¦ó¼g??
ÁÂÁÂ
y = Sheet1.Range("A1").End(xlDown).Row+1
Set Sh9R = Sheets("Sheet9").Rows(1)
Set Sh1R = Sheets("Sheet1").Rows(x)

Sh9R.Copy
With Sh1R
  .PasteSpecial Paste:=xlPasteFormats
  .PasteSpecial Paste:=xlPasteValidation
End With

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-10-31 22:27 ½s¿è
½Ð°Ý luhpro ¤j
¦pªG§Úªº­n¶Kªº¦a¤è¬OÅܼÆ,¬O¤£¬O¤£¯à³o¼Ë¼g
¦]¬°§Ú°õ¦æ·|¦³°ÝÃD
¥¿½Tªº¤è¦¡¬O­n¦p¦ó¼g ...
wind6424 µoªí©ó 2011-10-28 09:56

¨Ï¥Î With ... End With ªºÃöÁä´N¬O ±N "­«½Æ¨Ï¥Î" ªº¤¸¥óÂÇ¥Ñ With «Å§i,
¦Ó¯à§ï¥H . ¨Ó¨ú¥N¥H´î¤Öµ{¦¡ªº¤j¤p¤Î§PŪ(©Î¸ÑªR)ªº½ÆÂø«×.
¦³ÂI¹³DOS«ü¥O¥Î * ¨Ó¨ú¥N¬Y¤@ "¤£½T©w¤j¤p»P½d³ò" ªº¤å¦r²Õ¦X.

y = Sheet1.Range("A1").End(xlDown).Row+1
    (¦¹³B§Ú·|§ï¬° y = Sheets("Sheet1").Range("A1").End(xlDown).Row+1 ­ì¦]¸Ô°Ñ #39)
»P©³¤U Sh1R ªº
Sheets("Sheet1").Rows(x)
¨âªÌ¦@¦³ªº³¡¥÷´N¬O "Sheets("Sheet1")" ¤F.

¬G¦Ó§Ú­Ì¥i¥H§ï¬° :
Set Sh9R = Sheets("Sheet9").Rows(1)

With Sheets("Sheet1")
  y = .Range("A1").End(xlDown).Row+1
  Sh9R.Copy
  With .Rows(x)
    .PasteSpecial Paste:=xlPasteFormats
    .PasteSpecial Paste:=xlPasteValidation
  End With
End With

¤£¹L¦]¬°³o­Ó°Q½×¦ê¬O±Mªù°Q½× "VBAªº¼g§@§Þ¥©»P¼W¶i®Ä¯à" ªº,
¬°ÁקK¼Ò½k¤F­º½g¤å³¹ªº°Q½×µJÂI,
­YÁٻݭnÄ~Äò°Q½×«Øij½Ð§A¥t¶}·s¦ê°Q½×¬°©y(§A¥i¥H¦b¶}¦ê¤å­º¥´¤W:
¦¹½g¬°©µÄò "VBAªº¼g§@§Þ¥©»P¼W¶i®Ä¯à" ¦ê¤¤²Ä XX ½gªº°Q½×<«Øij­n¥[¤W³sµ²>),
´N¥i¥H¶¶§Q±µÄò¤U¥h°Q½×¤F.

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD