-
Aug 24th, 2017, 11:04 AM
#1
Thread Starter
New Member
[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
-
Aug 24th, 2017, 11:53 AM
#2
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
-
Aug 24th, 2017, 01:26 PM
#3
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|