Stuck and just cant work this out...HELP!!

atishk87

New Member
Joined
May 31, 2016
Messages
33
I have a set of data which our team records (shown below) in order for us to upload it into another system we work with all lines based per month with value needs to be listed below each other (2nd table) how can I get VBA to do this???

ClassFpnMfrQtyCostCurrencyMpnReqDate(yyyy/mm/dd)QuoteNumberNOVDECJANFEBMARCHAPRILMAYJUNE
4Blm317tnsc 0.65USDlm317t 10001000100010000000
4SFM28V100cyp 12.5USDFM28V10 00011701170117011701170
4SFM25V0cyp 5.7USDFM25V05 11641164116411641164116411641164
4B45LLTFITRcyp 0.6USD45ZXIT 0001200012000120001200012000

<tbody>
</tbody><colgroup><col style="text-align: center;" span="6"><col style="text-align: center;"><col style="text-align: center;" span="2"><col style="text-align: center;" span="4"><col style="text-align: center;" span="4"></colgroup>



ok so for each month that contains a value greater than 0 per line, I need it to duplicate that line as shown below, can this be done?
ClassFpnMfrQtyCostCurrencyMpnReqDate(yyyy/mm/dd)
09lm317tnsc10000.65000USDlm317t2016/11/01
09lm317tnsc10000.65000USDlm317t2016/12/01
09lm317tnsc10000.65000USDlm317t2017/01/01
09lm317tnsc10000.65000USDlm317t2017/02/01

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hello atishk, can you clarify what you are asking? If I'm understanding you correctly, you are wanting VBA to look at each of the values under the Month columns. If the value is greater than 0, you want the data from the first 8 columns to be duplicated in a second table. This needs to be done for every occurrence where the value in the Month columns is greater than 0. Is this correct? Also, where are both tables located? Are they on separate sheets in the same workbook, two different workbooks, etc.? Finally, are they actually formatted as Excel tables, or are they just lists of values placed in cells? Neither method is wrong, I just need to know how it's arranged in order to best help you. Also, in your example, it appears that the Class for lm317t changed from 4B to 09. Is there some reason for this?
 
Upvote 0
Hello atishk, can you clarify what you are asking? If I'm understanding you correctly, you are wanting VBA to look at each of the values under the Month columns. If the value is greater than 0, you want the data from the first 8 columns to be duplicated in a second table. This needs to be done for every occurrence where the value in the Month columns is greater than 0. Is this correct?

Hi Veritan,

That is correct, that's exactly what I want it to do.

Also, where are both tables located? Are they on separate sheets in the same workbook, two different workbooks, etc.? Finally, are they actually formatted as Excel tables, or are they just lists of values placed in cells? Neither method is wrong, I just need to know how it's arranged in order to best help you. Also, in your example, it appears that the Class for lm317t changed from 4B to 09. Is there some reason for this?

The output can be a new sheet on the same workbook.

In terms of formatting they are just values placed in cells.

In regards to the 4B changing to 09 that was my typing error in the example.

Thank you in advance for your help
 
Last edited:
Upvote 0
Okay, try this code and see if it does what you need. The sheet names and range references may need updated depending on where you want the data. You'll have to add the second sheet first before running the code. Also, where you have the names of the months in your original table, I made those the actual date (Nov. 1, Dec. 1, etc.) and then used a custom format of "mmmm" to display them. You'll need to do the same thing, though you can use any display format you like so long as the information is an actual date instead of just the text name of the month.

Code:
Sub CopyData()
    Dim r As Range, CopyDate As Date
    Application.ScreenUpdating = False
    Sheets("Sheet1").Activate
    For Each r In Range("J2", Cells(Rows.Count, 17).End(xlUp))
        If r > 0 Then
            CopyDate = Cells(1, r.Column)
            Cells(r.Row, 1).Resize(1, 7).Copy Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            With Sheets("Sheet2")
                .Cells(Rows.Count, 4).End(xlUp).Offset(1) = r
                .Cells(Rows.Count, 8).End(xlUp).Offset(1) = CopyDate
            End With
        End If
    Next r
    [A2].Select
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Okay, try this code and see if it does what you need. The sheet names and range references may need updated depending on where you want the data. You'll have to add the second sheet first before running the code. Also, where you have the names of the months in your original table, I made those the actual date (Nov. 1, Dec. 1, etc.) and then used a custom format of "mmmm" to display them. You'll need to do the same thing, though you can use any display format you like so long as the information is an actual date instead of just the text name of the month.

Code:
Sub CopyData()
    Dim r As Range, CopyDate As Date
    Application.ScreenUpdating = False
    Sheets("Sheet1").Activate
    For Each r In Range("J2", Cells(Rows.Count, 17).End(xlUp))
        If r > 0 Then
            CopyDate = Cells(1, r.Column)
            Cells(r.Row, 1).Resize(1, 7).Copy Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            With Sheets("Sheet2")
                .Cells(Rows.Count, 4).End(xlUp).Offset(1) = r
                .Cells(Rows.Count, 8).End(xlUp).Offset(1) = CopyDate
            End With
        End If
    Next r
    [A2].Select
    Application.ScreenUpdating = True
End Sub

WOW...works perfect...thank you soooo much
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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