• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to look at range of cells in row and perform paste to another sheet based on cell values

Jack Farhi

New Member
This is some what of a challenge for me - have been doing VBA for a while so bear with me please. Any help would be appreciated. What i'm trying to do is to take a named range and take 2 values from each row and perform a function. With the value in Column I, i want to paste that to a new row on another sheet and use the value in Column F as the number of times to paste the value from Column I on the other sheet in the adjacent cells (on the same row). For example. if the value in Column F is $95,000 and the value in Column I is 12, then i want to past the $95,000 12 times on the new sheet on a single row in columns A through L. I believe that this is a nested VBA function to handle the read for each of the 2 columns in the sequential rows at the source and then paste the results to the destination sheet and then cycle through again to the next row at the source and paste on the next row at the destination. Here's what i have thus far (far from finished). Any help would be appreciated!!!

Code:
Sub DeliveryDatesInflowOthers()
'
' DeliveryDatesInflowOthers Macro
'
    Application.ScreenUpdating = False
    Dim IngCol As Long
    Dim i As Integer
    Dim n As Integer
    IngCol = Range("XCol2")
    CleanSlateInflowsOther
    NumPMTs = Range("PmtQty")
    InflowSpacing = Range("PmtSpacing")
    Sheets("Inflows - OTHER").Select
    Range("I3").Select
    Selection.Copy
    Sheets("Cashflow").Select
   
    For n = 1 To 20
   
    Cells(44, IngCol).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    For i = 1 To NumPMTs - 1
    ActiveCell.Offset(0, InflowSpacing).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Next i
   
    Next n
   
    Sheets("Inflows").Select
    Range("A1").Select
    Sheets("Cashflow").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

I know that this works for a single row from source sheet. I tested it using single cell ranges - just need to cycle through this for each row at the source and to the destination. Ive coded the number of rows at the source from 1 to 20 - just an fyi.
 
Hi,

First of all, welcome to the forum :)

Please refer to the sample attached:
Code:
Sub CopyRng()

    Dim lrow, dest_lrow, n As Integer
    Dim c As Range
   
    lrow = Sheets(1).Cells(Rows.Count, "F").End(xlUp).Row
   
    For Each c In Sheets(1).Range("F1:F" & lrow)
        n = c.Offset(, 3).Value
        dest_lrow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
        Sheets(2).Range(Sheets(2).Cells(dest_lrow, 1), Sheets(2).Cells(dest_lrow, n)).Value = c.Value
    Next c

End Sub

Let me know if this helps
 

Attachments

  • Test.xlsm
    19.3 KB · Views: 5
Hi,

First of all, welcome to the forum :)

Please refer to the sample attached:
Code:
Sub CopyRng()

    Dim lrow, dest_lrow, n As Integer
    Dim c As Range
  
    lrow = Sheets(1).Cells(Rows.Count, "F").End(xlUp).Row
  
    For Each c In Sheets(1).Range("F1:F" & lrow)
        n = c.Offset(, 3).Value
        dest_lrow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
        Sheets(2).Range(Sheets(2).Cells(dest_lrow, 1), Sheets(2).Cells(dest_lrow, n)).Value = c.Value
    Next c

End Sub

Let me know if this helps

PCosta87 - YOU ARE A GOD!!! Looks good. Going to insert into my workbook and fine tune a bit - Sir - you are a scholar and a gentleman - my respect and thank you to you sir!!!!
 
So quick question, how can i use vba to specify which row to start the paste for sheet 2? Also, can i use a variable to declare which column to start on for each row that i paste in sheet 2?
 
Last edited:
Hi,

You are welcome... I'm glad I could help :)

how can i use vba to specify which row & column to start on sheet 2?

In this line:
Code:
Sheets(2).Range(Sheets(2).Cells(dest_lrow, 1), Sheets(2).Cells(dest_lrow, n)).Value = c.Value

dest_lrow is the last used row of the destination sheet and 1 is the column (1 stands for column "A").
dest_lrow basically detects the last used cell in the column specified (in this case "A" or 1).

Changing 1 to 2 will start at column B, below the last non empty cell... if you do this you will also need to adjust the end cell by adding the same value:

Example:
Imagine you wish to start at column D:
Code:
Sheets(2).Range(Sheets(2).Cells(dest_lrow, 1+3), Sheets(2).Cells(dest_lrow, n+3)).Value = c.Value

The row is dynamic so if you use a column where row 10 is the last one <>empty, it will start placing the values at row 11.

I suggest you fiddle with the code yourself and see the results of your changes... it will start to make sense :)
 
I'm beginning to understand now - thanks. Just one problem - when i insert the last line you specified it does not cycle through the rest of the values on sheet 1. Let's say that i add a third column in Sheet 1 that specifies which column to start in, how would that work with the line below?:

Code:
Sheets(2).Range(Sheets(2).Cells(dest_lrow, 1+3), Sheets(2).Cells(dest_lrow, n+3)).Value = c.Value
 
Ok - so let me clarify - i would like to define the starting row/column in destination sheet 2 for the start of the paste and then for each of the following rows in sheet 2, i would like to specify the starting column based on a value - so i tried to make the changes that you described above and it only worked for 1 line - i fiddled with the code a bit but went down a rathole -lol. Any other advice would be greatly appreciated!
 
Ok - so let me clarify - i would like to define the starting row/column in destination sheet 2 for the start of the paste and then for each of the following rows in sheet 2, i would like to specify the starting column based on a value - so i tried to make the changes that you described above and it only worked for 1 line - i fiddled with the code a bit but went down a rathole -lol. Any other advice would be greatly appreciated!
Hi,

Please upload a sample Excel file and I will gladly take a look at it.
What you want may need some tweaks or a complete overhaul of the current code...

Thanks
 
Sure thing - i understand. Attached is the sample file you requested. Looking to line up the output on (2) into the correct column specified on (1). Thanks for your help!!!
 

Attachments

  • Example.xlsm
    23.5 KB · Views: 1
That's perfect!!....Now - instead of starting all of the data in A1 on (2), can i specify what cell to start in?
 
It's great!! I just want to place it in something other than column A. - i would say column D as the starting point on (2)
 
It's great!! I just want to place it in something other than column A. - i would say column D as the starting point on (2). So basically, shifting the entire section over to cell D5 as the starting point for the columns and the rows.
 

Attachments

  • Picture.jpg
    Picture.jpg
    275.4 KB · Views: 4
PCosta87 - Eureka!! You sir are to be respected and celebrated. I thank you sincerely for your efforts - it's right on the nose. How may i repay you sir?
 
Back
Top