copy and paste cell vba help

hi all,

need some help with my vba in excel if any of you could please lend your expertise.

I have a cell on worksheet: "Avg Daily Vol", A5.
The cell would contain a date. i.e. "March 16 2015".

on sheet: "Avg Daily Vol"
if column A contains the text "test data", I'd like to copy the cell from B on that row.

then i'd like to paste the values on worksheet "VS". on VS, I have a calendar where it has the day.

i.e. B8-K8 lists 1,2,3,4,5,6,7,8,9,10 (each in a different cell).
B11-K11 lists 11-20 (each different cell)
B14-K14 lists 21-30 (each different cells)
B17 lists 31.

Based on the date listed above:
worksheet: "Avg Daily Vol", A5.
The cell would contain a date. i.e. "March 16 2015".

I'd like to paste the values under it's corresponding day. So if cell contains "March 16 2015", I want to copy and paste the data on the cell UNDER G11 (which says 16 in the cell). pasting the values in G12.

I hope this isn't too complicated.

thanks!
jfrank85Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Would be a lot simpler to understand with some sample data.

Can you upload a dummy file?

Thanks
Rob
0
jfrank85Author Commented:
yes, absolutely.  see attached.
Book4.xlsx
0
Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for..

Sub copydata()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim rng As Range, r As Range
    Set ws = Sheets("Avg Daily Vol")
    Set ws1 = Sheets("VS")

    Set rng = ws1.Range("B8:K8,B11:K11,B14:K14,B17")
    MsgBox Day(ws.Range("a5").Value)

    Set r = rng.Find(What:=Day(ws.Range("a5").Value), After:=ws1.Range("B8"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    r.Offset(1, 0).Value = ws.Range("b5").Value

End Sub

Open in new window


Saurabh...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jfrank85Author Commented:
thanks!

i tried to modify it, as i run the macro from a different workbook.

i tested and it's finding the workbook correctly, however under 'test copy/paste for data i'm getting stuck. i get an error on set ws= Sheets("Avg Daily Vol").
run-time error 9. subscript out of range.

Sub copydata()
Dim strSourcePathname As String, Report As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim wbForCopy As Workbook

    Set Report = ActiveWorkbook

    strSourcePathname = Report.Worksheets(1).Range("A98").Text & ".xlsx"
    strSourcePathname = Trim(strSourcePathname)
    MsgBox "Good, " & strSourcePathname & " opened."
    Set wbForCopy = IsOpen(strSourcePathname)
    If Not wbForCopy Is Nothing Then
    
    'test copy/paste for data
    Set ws = Sheets("Avg Daily Vol")
    Set ws1 = Sheets("VS")
  
    Set rng = ws1.Range("B8:K8,B11:K11,B14:K14,B17")
    MsgBox Day(ws.Range("a5").Value)

    Set r = rng.Find(What:=Day(ws.Range("a5").Value), After:=ws1.Range("B8"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    r.Offset(1, 0).Value = ws.Range("b5").Value
 Else
        MsgBox "Today's report is currently not opened."
    End If
End Sub

Open in new window

0
Saurabh Singh TeotiaCommented:
I don't see you declaring ws anywhere in the code.. declare the same as worksheet by...

dim ws as worksheet

Open in new window

0
jfrank85Author Commented:
still subscript out of range.

Set ws = Sheets("Avg Daily Vol")
    Set ws1 = Sheets("VS")

these sheets are on wbForCopy.

Should i have wbForCopy.Sheets("Avg Daily Vol") ?

I tried that and still get subscript out of range.
0
Saurabh Singh TeotiaCommented:
Yes that should take care of it but i don't see in your code where you are opening that workbook
0
jfrank85Author Commented:
it's already opened manually. my code detects the open workbook just fine...

it's then getting stuck.


Sub copydata()
    Dim strSourcePathname As String, Report As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim wbForCopy As Workbook
    Dim ws As Worksheet
    
    Set Report = ActiveWorkbook

    strSourcePathname = Report.Worksheets(1).Range("A98").Text & ".xlsx"
    strSourcePathname = Trim(strSourcePathname)
    MsgBox "Good, " & strSourcePathname & " opened."
    Set wbForCopy = IsOpen(strSourcePathname)
    If Not wbForCopy Is Nothing Then
    
    'test copy/paste for data
    Set ws = wbForCopy.Sheets("Avg Daily Vol")
    Set ws1 = wbForCopy.Sheets("VS")
  
    Set rng = ws1.Range("B8:K8,B11:K11,B14:K14,B17")
    MsgBox Day(ws.Range("a5").Value)

    Set r = rng.Find(What:=Day(ws.Range("a5").Value), After:=ws1.Range("B8"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    r.Offset(1, 0).Value = ws.Range("b5").Value
 Else
        MsgBox "Today's report is currently not opened."
    End If
End Sub

Open in new window

0
Saurabh Singh TeotiaCommented:
Can you post me the function of isopen .. so that i can look into it and make necessary changes accordingly..
0
jfrank85Author Commented:
Function IsOpen(wbname As String) As Workbook

    Dim wb As Workbook

    For Each wb In Application.Workbooks
        If wb.Name = wbname Then
            'MsgBox "Found open workbook"
            Set IsOpen = wb
            Exit For
        End If
    Next wb
End Function

Open in new window

0
Saurabh Singh TeotiaCommented:
Can you stop the code at
    Set wbForCopy = IsOpen(strSourcePathname)

and see does wbforcopy has any value in it or it's not blank??

Then again help me understand the sheet name you are writing and the sheet name in the code is exactly the same, their is no difference of spaces or anything else..
0
jfrank85Author Commented:
sure, the open workbook's name is on the macro workbook's Range("A98").Text & ".xlsx"

strSourcePathname = "March 18 2015.xlsx", so that's the workbook i want to do all the work for copy/paste.

wbForCopy = Nothing
0
Saurabh Singh TeotiaCommented:
When you select over wbforcopy..does it says nothing that means it's not reading the name of the workbook or not able to find that workbook in your range("A98")..try changing the format in this line..

strSourcePathname = Report.Worksheets(1).Range("A98").Text & ".xlsx"

to this..

strSourcePathname = format(Report.Worksheets(1).Range("A98").value,"mmmm dd yyyy") & ".xlsx"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfrank85Author Commented:
perfect, thanks for that!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.