Results 1 to 3 of 3

Thread: [EXCEL] Copying a string to 2 different sheets in a separate workbook.

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    2

    [EXCEL] Copying a string to 2 different sheets in a separate workbook.

    I'm using MS office, Excel VBA 7.1 ("Retail 7.1.1068"). Windows 10 up-to-date.
    My first post here, have looked for answers.
    I'm working with some old code, so apologies for it looking simplistic.
    Just need to have a macro that copies a string value in a single cell from an open workbook, to a different workbook, on two different worksheets in the target workbook, at the bottom of column "B" in both instances. I've tried about six ways to do this but the simplest code looks like:

    Code:
    Dim strValue As String 
        Windows("WorkBook1.xlsm").Activate
        Worksheets("Sheet1").Range("D7").Activate  ' or Range("D7").Select
        strValue = ActiveCell.Value
        
        Windows("Workbook2.xls").Activate
        Set wSht = TargetWB.Sheets("Worksheet2")
        Set TargetCell = wSht.Range("B65536").End(xlUp).Offset(1, 0)
        TargetCell.Value = strValue
        Set wSht = TargetWB.Sheets("Worksheet3")
        Set TargetCell = wSht.Range("B65536").End(xlUp).Offset(1, 0)
        TargetCell.Value = strValue
    The data in "D7" is a string. The first target in Worksheet2 works. The second targeted cell puts an integer from a IntValue2 defined *later* in the macro. The integer is in the correct position at the bottom of Worksheet3, column B.

    Tried:
    1. reactivating Workbook2.xls
    2. strValue = Range("D7").MergeArea.Cells(1, 1).Value
    3. Worksheets("Worksheet3").Activate
    3. defining and using strValue2 for Worksheet3
    4. NOT using "set" at all
    etc.
    It must be simple, but I'm not a great coder (actually I'm a lousy coder). Can't understand why this won't work. The integer that shows up is a real mystery, because there are no other references to Worksheet3, column B in the macro.
    Thanks,
    Pete

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [EXCEL] Copying a string to 2 different sheets in a separate workbook.

    something like:

    Code:
    Sub copyIt()
        Dim strValue As String
        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim ws2 As Worksheet
        Dim ws3 As Worksheet
        
        Set wb1 = ThisWorkbook
        strValue = wb1.Worksheets("Sheet1").Range("d7").Value
        
        Set wb2 = Workbooks("workbook2")
        Set ws2 = wb2.Worksheets("worksheet2")
        Set ws3 = wb2.Worksheets("worksheet3")
        
        ws2.Range("b" & Rows.Count).End(xlUp).Offset(1, 0).Value = strValue
        ws3.Range("b" & Rows.Count).End(xlUp).Offset(1, 0).Value = strValue
    End Sub

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    2

    Re: [EXCEL] Copying a string to 2 different sheets in a separate workbook.

    Thanks, will try tonight and give update.
    EDIT:
    Tried it and it got me past this roadblock, but for some reason I had to use:
    Code:
        ws2.Range("B65536").End(xlUp).Offset(1, 0).Value = strValue
        ws3.Range("B65536").End(xlUp).Offset(1, 0).Value = strValue
    Very much appreciated. On to the next hack.
    Pete
    Last edited by hoytpr; Aug 24th, 2017 at 01:52 PM. Reason: obvious

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width