Paste in correct column based on criteria

[luke1438]

I am attempting to Copy.Paste.Value from the "Entry Calc Sheet" to the "Cash Flow" sheet the total revenue number. The paste needs to be in the correct column based off of a date reference code ("Period Ending") on "Entry Calc Sheet". The "Cash Flow" sheet has the date reference codes in each column across the top (row highlighted in purple) and the paste needs to be in row 14 on the "Cash Flow" sheet in the column with the cooresponding date reference code. I have attached a copy of the file and left copies of the macros I have tried so far - none of which come even close .... my VBA skills are below beginer.

I would appreciate any help or advice.

Thanks in advance
Luke

[Bob Phillips]

[vba]

Dim iCol As Long

With Worksheets("Entry Calc Sheet")
iCol = Application.Match(.Range("C7").Value, _
Worksheets("Cash Flow").Rows(9), 0)
Worksheets("Cash Flow").Cells(14, iCol).Value = .Range("C9").Value
End With
[/vba]

[luke1438]

Wow! Thanks, I've been working on that all day.

[luke1438]

I have now added a couple of new sheets and am trying to copy and paste value a range of cells; B9:B112 from the "Entry Sheet". I have modified the code as follows:

Sub Macro_3()
Dim iCol As Long
With Worksheets("Entry Calc Sheet")
iCol = Application.Match(.Range("C7").Value, _
Worksheets("Monthly Backup Data").Rows(6), 0)
Worksheets("Monthly Backup Data").Cells(14, iCol).Value = Worksheets("Entry Sheet").Range("B9:B112").Value
End With
End Sub

However it is not doing anything; Can I mix sheets like this? Do I have to change the paste section to include a range also? Any help or advice would be greatly appreciated.

Thanks
Luke

[herzberg]

Hmmm.... This is what I would do:

[vba] With Worksheets("Entry Calc Sheet")
.Range("B9:B112").Copy Worksheets("Monthly Backup Data").Range("A1")
End With[/vba]
This pastes the data into cell A1 of the Monthly Backup Data sheet; you can change the target sheet name and target cell to suit your needs.

[Bob Phillips]

I assume Entry Sheet and Entry Calc Sheet are the same sheet, it was just a typo

[vba]

Sub Macro_3()
Dim iCol As Long
With Worksheets("Entry Calc Sheet")
iCol = Application.Match(.Range("C7").Value, _
Worksheets("Monthly Backup Data").Rows(6), 0)
.Range("B9:B112").Copy Worksheets("Monthly Backup Data").Cells(14, iCol)
End With
End Sub
[/vba]

[luke1438]

Actually no, they are two different sheets. Can I mix sheets like that within VBA?

[Bob Phillips]

Yes you can , I just thought that because the names were so similar that they were the same. In my example you would use

[vba]

Sub Macro_3()
Dim iCol As Long
With Worksheets("Entry Calc Sheet")
iCol = Application.Match(.Range("C7").Value, _
Worksheets("Monthly Backup Data").Rows(6), 0)
Worksheets("Entry Sheet").Range("B9:B112").Copy Worksheets("Monthly Backup Data").Cells(14, iCol)
End With
End Sub
[/vba]

[luke1438]

Got it! I changed cell A1 in the "Entry Sheet" to bring in the date code from the "Entry Calc Sheet" and it works fine. Thank you for all your help.

Luke

Changed Code:
Sub Macro_3()
Dim iCol As Long
With Worksheets("Entry Sheet")
iCol = Application.Match(.Range("A1").Value, _
Worksheets("Monthly Backup Data").Rows(6), 0)
.Range("B10:B113").Copy Worksheets("Monthly Backup Data").Cells(14, iCol)
End With
End Sub

[luke1438]

Thanks for the update; two more questions if you don't mind.

Why can't I add .Value to make it paste value? I have tried adding that in every place and it comes up with an error each time.

Also, what would be a good book to learn this stuff?

Thanks
Luke

[Bob Phillips]

You can't add .Value because it is not pasting, it is copying.

Good book, depends upon what you want, and what level you are, or think you are, at. Go to a good bookshop and browe and see which one comes across as well written as is informative to you.

[luke1438]

What can I do to make the data from the "Entry Sheet" show up in the "Monthly Backup Data" sheet without the formating?

[Bob Phillips]

Could you not just take it off?