[belajar-excel] Tanya Vba

Kelikpitoyo [email protected] [belajar-excel] Wed, 14 Mar 2018 23:11:59 -0700

Dear Masters,

Selamat Pagi,


Mohon dibantu,

saya belajar record macro, dari file "Source Format" saya mau copy paste ke 
file baru "Book9"

Namun saya tambahkan baris di tiap data yang saya copy, dengan menambahkan kata 
"Jasa Maklon" dan "500".

Jumlah baris data yang dicopy berubah-ubah,

Bagaimana bentuk script VBAnya? Untuk tidak menambahkan “Jasa Maklon” dan “500” 
saat tidak ada data yang dicopy,

mohon pencerahannya,

Berikut saya lampirkan:

file source = Source Format,

file hasil copy = Book9,  dan record makro nya,

Terima kasih sebelumnya,

Salam

Kelik

=EF=BB=BF<?xml version=3D"1.0"?>
<?mso-application progid=3D"Excel.Sheet"?>
<Workbook xmlns=3D"urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o=3D"urn:schemas-microsoft-com:office:office"
 xmlns:x=3D"urn:schemas-microsoft-com:office:excel"
 xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html=3D"http://www.w3.org/TR/REC-html40";>
 <DocumentProperties xmlns=3D"urn:schemas-microsoft-com:office:office">
  <Author>Vendor System for AHM</Author>
  <LastAuthor>Kurnia Sembada</LastAuthor>
  <Created>2014-08-13T09:33:21Z</Created>
  <LastSaved>2014-08-13T09:43:46Z</LastSaved>
  <Company>LISENSI-SOFTWARE.com</Company>
  <Version>12.00</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns=3D"urn:schemas-microsoft-com:office:excel">
  <SupBook>
   <Path> </Path>
   <SheetName> </SheetName>
  </SupBook>
  <WindowHeight>7935</WindowHeight>
  <WindowWidth>20115</WindowWidth>
  <WindowTopX>240</WindowTopX>
  <WindowTopY>135</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID=3D"Default" ss:Name=3D"Normal">
   <Alignment ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Calibri" x:Family=3D"Swiss" ss:Size=3D"11" =
ss:Color=3D"#000000"/>
  </Style>
  <Style ss:ID=3D"s62" ss:Name=3D"Normal 2">
   <Alignment ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Calibri" x:Family=3D"Swiss" ss:Size=3D"11" =
ss:Color=3D"#000000"/>
  </Style>
  <Style ss:ID=3D"s63">
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#000000"/>
  </Style>
  <Style ss:ID=3D"s64">
   <Alignment ss:Horizontal=3D"Center" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#000000"/>
  </Style>
  <Style ss:ID=3D"s66" ss:Parent=3D"s62">
   <Alignment ss:Horizontal=3D"Left" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#000000"/>
   <NumberFormat ss:Format=3D"@"/>
  </Style>
  <Style ss:ID=3D"s67" ss:Parent=3D"s62">
   <Alignment ss:Horizontal=3D"Center" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#FFFFFF"/>
   <Interior ss:Color=3D"#8DB4E2" ss:Pattern=3D"Solid"/>
   <NumberFormat ss:Format=3D"[ENG][$-409]d\-mmm\-yy;@"/>
  </Style>
  <Style ss:ID=3D"s68" ss:Parent=3D"s62">
   <Alignment ss:Horizontal=3D"Center" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#FFFFFF"/>
   <Interior ss:Color=3D"#8DB4E2" ss:Pattern=3D"Solid"/>
   <NumberFormat ss:Format=3D"@"/>
  </Style>
  <Style ss:ID=3D"s69" ss:Parent=3D"s62">
   <Alignment ss:Horizontal=3D"Left" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#FFFFFF"/>
   <Interior ss:Color=3D"#8DB4E2" ss:Pattern=3D"Solid"/>
   <NumberFormat ss:Format=3D"@"/>
  </Style>
  <Style ss:ID=3D"s70" ss:Parent=3D"s62">
   <Alignment ss:Horizontal=3D"Left" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#FFFFFF"/>
   <Interior ss:Color=3D"#8DB4E2" ss:Pattern=3D"Solid"/>
   <NumberFormat ss:Format=3D"dd/mm/yyyy;@"/>
  </Style>
  <Style ss:ID=3D"s71" ss:Parent=3D"s62">
   <Alignment ss:Horizontal=3D"Right" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#FFFFFF"/>
   <Interior ss:Color=3D"#8DB4E2" ss:Pattern=3D"Solid"/>
   <NumberFormat ss:Format=3D"dd/mm/yyyy;@"/>
  </Style>
  <Style ss:ID=3D"s72" ss:Parent=3D"s62">
   <Alignment ss:Horizontal=3D"Center" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#FFFFFF"/>
   <Interior ss:Color=3D"#8DB4E2" ss:Pattern=3D"Solid"/>
   <NumberFormat ss:Format=3D"dd/mm/yyyy;@"/>
  </Style>
  <Style ss:ID=3D"s73">
   <Alignment ss:Horizontal=3D"Center" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#000000"/>
   <NumberFormat ss:Format=3D"Short Date"/>
  </Style>
  <Style ss:ID=3D"s74">
   <Alignment ss:Horizontal=3D"Center" ss:Vertical=3D"Bottom"/>
   <Font ss:FontName=3D"Tahoma" x:Family=3D"Swiss" =
ss:Color=3D"#000000"/>
   <NumberFormat ss:Format=3D"General Date"/>
  </Style>
 </Styles>
 <Worksheet ss:Name=3D"INQUIRY_DELIVERY">
  <Table ss:ExpandedColumnCount=3D"10" ss:ExpandedRowCount=3D"11" =
x:FullColumns=3D"1"
   x:FullRows=3D"1" ss:StyleID=3D"s63">
   <Column ss:StyleID=3D"s64" ss:Width=3D"77.25"/>
   <Column ss:StyleID=3D"s64" ss:Width=3D"95.25"/>
   <Column ss:StyleID=3D"s64" ss:Width=3D"72"/>
   <Column ss:StyleID=3D"s64" ss:Width=3D"51" ss:Span=3D"1"/>
   <Column ss:Index=3D"6" ss:StyleID=3D"s63" ss:Width=3D"116.25"/>
   <Column ss:StyleID=3D"s63" ss:Width=3D"119.25"/>
   <Column ss:StyleID=3D"s63" ss:Width=3D"66"/>
   <Column ss:StyleID=3D"s64" ss:Width=3D"95.25"/>
   <Column ss:StyleID=3D"s63" ss:Width=3D"75"/>
   <Row>
    <Cell ss:StyleID=3D"s66"><Data ss:Type=3D"String">Vendor System =
</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s66"><Data ss:Type=3D"String">Inquiry Delivery =
MFT</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index=3D"2" ss:StyleID=3D"s74" =
ss:Formula=3D"=3DNOW()"><Data
      ss:Type=3D"DateTime">2018-03-15T09:43:05.290</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s67"><Data ss:Type=3D"String">TGL. =
DELIVERY</Data></Cell>
    <Cell ss:StyleID=3D"s68"><Data ss:Type=3D"String">SURAT JALAN# =
(DN)</Data></Cell>
    <Cell ss:StyleID=3D"s67"><Data ss:Type=3D"String">PO#</Data></Cell>
    <Cell ss:StyleID=3D"s67"><Data =
ss:Type=3D"String">PLANT#</Data></Cell>
    <Cell ss:StyleID=3D"s67"><Data =
ss:Type=3D"String">GATE#</Data></Cell>
    <Cell ss:StyleID=3D"s69"><Data =
ss:Type=3D"String">PART#</Data></Cell>
    <Cell ss:StyleID=3D"s70"><Data =
ss:Type=3D"String">DESKRIPSI</Data></Cell>
    <Cell ss:StyleID=3D"s71"><Data ss:Type=3D"String">QTY</Data></Cell>
    <Cell ss:StyleID=3D"s68"><Data ss:Type=3D"String">DS</Data></Cell>
    <Cell ss:StyleID=3D"s72"><Data =
ss:Type=3D"String">STATUS</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s73"><Data =
ss:Type=3D"String">14/03/2018</Data></Cell>
    <Cell ss:Index=3D"3"><Data =
ss:Type=3D"String">5200033498A</Data></Cell>
    <Cell><Data ss:Type=3D"String">1400</Data></Cell>
    <Cell><Data ss:Type=3D"String">N/A</Data></Cell>
    <Cell><Data ss:Type=3D"Number">3040025600</Data></Cell>
    <Cell ss:Index=3D"8"><Data ss:Type=3D"Number">500</Data></Cell>
    <Cell ss:Index=3D"10" ss:StyleID=3D"s64"><Data =
ss:Type=3D"String">COMPLETED</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s73"><Data =
ss:Type=3D"String">14/03/2018</Data></Cell>
    <Cell ss:Index=3D"3"><Data =
ss:Type=3D"String">5200033498B</Data></Cell>
    <Cell><Data ss:Type=3D"String">1400</Data></Cell>
    <Cell><Data ss:Type=3D"String">N/A</Data></Cell>
    <Cell><Data ss:Type=3D"Number">3040025610</Data></Cell>
    <Cell ss:Index=3D"8"><Data ss:Type=3D"Number">139</Data></Cell>
    <Cell ss:Index=3D"10" ss:StyleID=3D"s64"><Data =
ss:Type=3D"String">COMPLETED</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s73"><Data =
ss:Type=3D"String">14/03/2018</Data></Cell>
    <Cell ss:Index=3D"3"><Data =
ss:Type=3D"String">5200033498C</Data></Cell>
    <Cell><Data ss:Type=3D"String">1400</Data></Cell>
    <Cell><Data ss:Type=3D"String">N/A</Data></Cell>
    <Cell><Data ss:Type=3D"Number">3160015901</Data></Cell>
    <Cell ss:Index=3D"8"><Data ss:Type=3D"Number">500</Data></Cell>
    <Cell ss:Index=3D"10" ss:StyleID=3D"s64"><Data =
ss:Type=3D"String">COMPLETED</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s73"><Data ss:Type=3D"String">14/03/2018 =
10:30:00 AM</Data></Cell>
    <Cell ss:Index=3D"3"><Data =
ss:Type=3D"String">5100026825D</Data></Cell>
    <Cell><Data ss:Type=3D"String">1400</Data></Cell>
    <Cell><Data ss:Type=3D"String">N/A</Data></Cell>
    <Cell><Data ss:Type=3D"Number">3040066000</Data></Cell>
    <Cell ss:Index=3D"8"><Data ss:Type=3D"Number">220</Data></Cell>
    <Cell ss:Index=3D"10" ss:StyleID=3D"s64"><Data =
ss:Type=3D"String">COMPLETED</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s73"><Data ss:Type=3D"String">14/03/2018 =
10:30:00 AM</Data></Cell>
    <Cell ss:Index=3D"3"><Data =
ss:Type=3D"String">5100026825E</Data></Cell>
    <Cell><Data ss:Type=3D"String">1400</Data></Cell>
    <Cell><Data ss:Type=3D"String">N/A</Data></Cell>
    <Cell><Data ss:Type=3D"Number">3041058320</Data></Cell>
    <Cell ss:Index=3D"8"><Data ss:Type=3D"Number">500</Data></Cell>
    <Cell ss:Index=3D"10" ss:StyleID=3D"s64"><Data =
ss:Type=3D"String">COMPLETED</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s73"><Data ss:Type=3D"String">14/03/2018 =
10:30:00 AM</Data></Cell>
    <Cell ss:Index=3D"3"><Data =
ss:Type=3D"String">5100026825F</Data></Cell>
    <Cell><Data ss:Type=3D"String">1400</Data></Cell>
    <Cell><Data ss:Type=3D"String">N/A</Data></Cell>
    <Cell><Data ss:Type=3D"Number">3160097100</Data></Cell>
    <Cell ss:Index=3D"8"><Data ss:Type=3D"Number">1500</Data></Cell>
    <Cell ss:Index=3D"10" ss:StyleID=3D"s64"><Data =
ss:Type=3D"String">COMPLETED</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID=3D"s73"><Data ss:Type=3D"String">14/03/2018 =
10:30:00 AM</Data></Cell>
    <Cell ss:Index=3D"3"><Data =
ss:Type=3D"String">5100026825G</Data></Cell>
    <Cell><Data ss:Type=3D"String">1400</Data></Cell>
    <Cell><Data ss:Type=3D"String">N/A</Data></Cell>
    <Cell><Data ss:Type=3D"Number">3160051000</Data></Cell>
    <Cell ss:Index=3D"8"><Data ss:Type=3D"Number">4000</Data></Cell>
    <Cell ss:Index=3D"10" ss:StyleID=3D"s64"><Data =
ss:Type=3D"String">COMPLETED</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns=3D"urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin=3D"0.3"/>
    <Footer x:Margin=3D"0.3"/>
    <PageMargins x:Bottom=3D"0.75" x:Left=3D"0.7" x:Right=3D"0.7" =
x:Top=3D"0.75"/>
   </PageSetup>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>12</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Sub transpose()
'
' transpose Macro
'
' Keyboard Shortcut: Ctrl+t
'
    Windows("Source Format.xml").Activate
    ActiveWindow.WindowState = xlNormal
    Range("C5:C50").Select
    Selection.Copy
    Workbooks.Open Filename:="C:\Users\XXXX\Desktop\Book9.xlsx"
    Windows("Book9.xlsx").Activate
    Range("C7").Select
    ActiveSheet.Paste
    Columns("C:C").ColumnWidth = 17
    Windows("Source Format.xml").Activate
    Range("D5:F50").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book9.xlsx").Activate
    Range("X7").Select
    ActiveSheet.Paste
    Columns("F:F").ColumnWidth = 17
    Windows("Source Format.xml").Activate
    Range("F5:F50").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book9.xlsx").Activate
    Range("F7").Select
    ActiveSheet.Paste
    Columns("F:F").ColumnWidth = 17
    Windows("Source Format.xml").Activate
    Range("H5:H50").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book9.xlsx").Activate
    Range("I7").Select
    ActiveSheet.Paste
    Rows("8:8").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "Jasa Maklon"
    Range("I8").Select
    ActiveCell.FormulaR1C1 = "500"
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "Jasa Maklon"
    Range("I10").Select
    ActiveCell.FormulaR1C1 = "500"
    Rows("12:12").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F12").Select
    ActiveCell.FormulaR1C1 = "Jasa Maklon"
    Range("I12").Select
    ActiveCell.FormulaR1C1 = "500"
    Rows("14:14").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "Jasa Maklon"
    Range("I14").Select
    ActiveCell.FormulaR1C1 = "500"
    Rows("16:16").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "Jasa Maklon"
    Range("I16").Select
    ActiveCell.FormulaR1C1 = "500"
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F18").Select
    ActiveCell.FormulaR1C1 = "Jasa Maklon"
    Range("I18").Select
    ActiveCell.FormulaR1C1 = "500"
    Rows("20:20").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F20").Select
    ActiveCell.FormulaR1C1 = "Jasa Maklon"
    Range("I20").Select
    ActiveCell.FormulaR1C1 = "500"
    With ActiveCell.Characters(Start:=1, Length:=11).Font
        .Name = "Tahoma"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("I16").Select
    With Selection.Font
        .Name = "Tahoma"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    ActiveCell.FormulaR1C1 = "500"
    Range("F17").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\XXXX\Desktop\Book9.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub

Book9.xlsx
Description: MS-Excel 2007 spreadsheet

[belajar-excel] Tanya Vba

Kirim email ke