asked on

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!

Rob Henson

Would be a lot simpler to understand with some sample data.

Can you upload a dummy file?

Thanks
Rob

jfrank85

ASKER

yes, absolutely. see attached.
Book4.xlsx

Saurabh Singh Teotia

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...

jfrank85

ASKER

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

Saurabh Singh Teotia

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

jfrank85

ASKER

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.

Saurabh Singh Teotia

Yes that should take care of it but i don't see in your code where you are opening that workbook

jfrank85

ASKER

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

Saurabh Singh Teotia

Can you post me the function of isopen .. so that i can look into it and make necessary changes accordingly..

jfrank85

ASKER

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

Saurabh Singh Teotia

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..

jfrank85

ASKER

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

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfrank85

ASKER

perfect, thanks for that!