+ Reply to Thread
Results 1 to 2 of 2

Copy Pasting to a new format Using Macro

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-20-2022
    Location
    Indonesia
    MS-Off Ver
    Excel 2016
    Posts
    118

    Smile Copy Pasting to a new format Using Macro

    I have a problem work with two different type of workbook with the same data in it, it is a bit time consuming if I do it with a manual typing or copy pasting.
    Is the any Solution to copy data to a different format with vba code.

    Note:
    the first format if Feedmaster## and the 2nd is the target (or copy to new worksheet in Feedmaster##)
    Attached Files Attached Files

  2. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2501
    Posts
    1,440

    Re: Copy Pasting to a new format Using Macro

    Maybe:
    Sub test()
        Dim rng As Range, var As Variant, dVar As Variant
        Dim hRng As Range, hVar As Variant
        Dim Itms As Long, x As Long, y As Long, d As Long
        
        Set rng = Range("B8:M" & Range("B" & Rows.Count).End(xlUp).Row)
        Set hRng = Range("B5:M5")
        hVar = hRng.Value: var = rng.Value
        Itms = Application.CountA(rng.Offset(, 3).Resize(, 9))
        
        ReDim dVar(Itms, 4)
        
        dVar(0, 0) = "UNIT"
        dVar(0, 1) = "TANGGAL"
        dVar(0, 2) = "RING"
        dVar(0, 3) = "PAKAN"
        dVar(0, 4) = "QTY"
        
        For x = 1 To UBound(var)
            For y = 4 To 12
                If var(x, y) > 0 Then
                    d = d + 1
                    dVar(d, 0) = var(x, 1)
                    dVar(d, 1) = var(x, 3)
                    dVar(d, 2) = var(x, 2)
                    dVar(d, 3) = hVar(1, y)
                    dVar(d, 4) = var(x, y)
                End If
            Next y
        Next x
        
        Worksheets.Add
        Range("A1").Resize(UBound(dVar) + 1, 5) = dVar
    End Sub
    If things don't change they stay the same

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel macro not pasting date format correctly.
    By jsmcdon1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-24-2021, 02:51 PM
  2. Macro to only allow pasting of values and not format??
    By Celt in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 01-26-2021, 01:45 PM
  3. VBA Macro for only pasting values OR match destination format
    By c.davidson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2017, 06:48 AM
  4. [SOLVED] Copy and Pasting Macro
    By markusvirus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2016, 09:27 AM
  5. [SOLVED] Copy/Pasting with a macro
    By tsakta13ole in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2015, 08:54 AM
  6. Replies: 2
    Last Post: 07-09-2008, 06:05 PM
  7. [SOLVED] Macro Copy and Pasting
    By Christine in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-23-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1