Copy data fom 1 workbook to another, where itneeds to go to 2 different worksheets

[Mr_Mod]

Hi,
I have a workbook (WB1) with a worksheet in it (WS1) which has values in cells D9, D10, K12, R18, B2, B3 which need to be moved to another workbook (WB2) with 2 worksheets (WS2 & WS3).
Data in cell D9 needs to go to WB2 WS2 cell A7, D1 needs to goto WB2 WS2 cell B7. Cell K12 needs to go to WB2 WS3 cell F12, cell R18 needs to go to WB2 WS2 & WS3 cell K4.
Cell B2 contains an value which corresponds to a location, this location needs to be looked up in a collumn in WB2 WS3 and cell in collumn F that is on the same row needs to be updated with the value B3

I just cant see how to get this to operate in VBA, i can open the other workbook easily but not how to copy data.

The other problem is that if row 7 already has data how would i be able to automatically move to row 8 then paste the data and so on as i paste data from other workbooks.

Help apreciated.

[mancubus]

not sure if i understand correctly. but try:

Sub CopyFrToWB()
    Dim wbF As Workbook, wbS As Workbook
    Dim fndCell As Range
    Dim searchStr As String
    Set wbF = Workbooks("WB1.xls")
    Set wbS = Workbooks("WB2.xls")
    With wbF.Worksheets("WS1")
        .Range("D9").Copy
        wbS.Worksheets("WS2").Range("A7").PasteSpecial Paste:=xlPasteAll
        .Range("D10").Copy
        wbS.Worksheets("WS2").Range("A8").PasteSpecial Paste:=xlPasteAll
        .Range("K12").Copy
        wbS.Worksheets("WS3").Range("F12").PasteSpecial Paste:=xlPasteAll
        .Range("R18").Copy
        wbS.Worksheets("WS2").Range("K4").PasteSpecial Paste:=xlPasteAll
        wbS.Worksheets("WS3").Range("K4").PasteSpecial Paste:=xlPasteAll
    End With
    Application.CutCopyMode = False
    searchStr = wbF.Worksheets("WS1").Range("B2").Value
    wbS.Activate
    Worksheets("WS3").Activate
    Set fndCell = Columns("F").Find(What:=searchStr, After:=[F1], _
    LookAt:=xlPart, SearchOrder:=xlByRows, LookIn:=xlValues, _
    SearchDirection:=xlNext, MatchCase:=False)
    If fndCell Is Nothing Then
        MsgBox "Search Value Not Found"
    Else
        fndCell.Value = wbF.Worksheets("WS1").Range("B3").Value
    End If
    Set wbF = Nothing
    Set wbS = Nothing
End Sub

[Mr_Mod]

Thanks,
The first part works fine where the cells get copied across, however im still confused as im trying to understand the process how it works.

I will try to explain clearer as to what im trying to achieve.

In WB1 WS1 Cell D9 = Company Name this will appear in Column E in WB2 WS1
In WB1 WS1 Cell E12 = Inspection Date this will appear in Column F in WB2 WS1
In WB1 WS1 Cell R18 = Site Address ID this will appear in Column A in WB2 WS1
In WB1 WS1 Cell K12 = Site Name this will appear in Column B in WB2 WS1

If Row 7 is already populated, the data would then need to be put into row 8 and so on down to row 2600 as there are approx 2600 sites records to be updated.

Now the next part I think i got confused in what i wrote,

In WB1 WS1 Cell R18 = Site Address ID if this value is found in column "A" in WB2 WS3 then i wish to update the date in column "M"

In WB1 WS1 cells F24 to F63 there is a drop down menu where a contractor will select 1 of 2 values being "Crack" or "No Crack" if any of these cells say "Crack" then the value in column "H" in WB2 WS2, however the value can only be changed if Site ID's correspond with the correct record. If "no crack" is found then the value in Column "H" would say "No Crack"

I have spent many hours reading various sites but get more confused each time, I havent done any programming in 25+ years and that was only assembler on a 6502 processor.

There is one more part which im trying to figure out which is the value found in D24 to 63 if it is equal to item list 2,5,8 then the value in WB2 WS2 column G would become a yes if it is other values it becomes a No. This part im sure i should be able to fathom once i get on top of the above.

Many thanks again

[mancubus]

first post:

Data in cell D9 needs to go to WB2 WS2 cell A7,
Data in cell D9 needs to go to WB2 WS2 cell A7,
D1 needs to goto WB2 WS2 cell B7,
K12 needs to go to WB2 WS3 cell F12,
R18 needs to go to WB2 WS2 & WS3 cell K4.

The other problem is that if row 7 already has data how would i be able to automatically move to row 8 then paste the data and so on as i paste data from other workbooks.

second post

Thanks,
In WB1 WS1 Cell D9 = Company Name this will appear in Column E in WB2 WS1
In WB1 WS1 Cell E12 = Inspection Date this will appear in Column F in WB2 WS1
In WB1 WS1 Cell R18 = Site Address ID this will appear in Column A in WB2 WS1
In WB1 WS1 Cell K12 = Site Name this will appear in Column B in WB2 WS1

If Row 7 is already populated, the data would then need to be put into row 8 and so on down to row 2600 as there are approx 2600 sites records to be updated.

you may adopt the given cell references to your real req's.


for the green part, which is missing in my 1st post:
adopt

With wbF.Worksheets("WS1")
    .Range("D9").Copy
    wbS.Worksheets("WS2").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll

this code is equal to =
- select cell A65536
- hit "ctrl" and "up arrow" keys at the same time (= goes to last row with data on column A)
- offset last filled cell by 1 row (= next empty cell)

you should adopt this to your needs.

[mancubus]

if the cell formats etc are not important

 
wbS.Worksheets("WS2").Range("A7") = wbF.Worksheets("WS1").Range("D9")
wbS.Worksheets("WS2").Range("A8") = wbF.Worksheets("WS1").Range("D10")
wbS.Worksheets("WS3").Range("F12") = wbF.Worksheets("WS1").Range("K12")
wbS.Worksheets("WS2").Range("K4") = wbF.Worksheets("WS1").Range("R18")
wbS.Worksheets("WS3").Range("K4") = wbF.Worksheets("WS1").Range("R18")

for last blank row

 
wbS.Worksheets("WS2").Range("A65536").End(xlUp).Offset(1, 0) = wbF.Worksheets("WS1").Range("D9")

 
wbS.Worksheets("WS2").Range("A65536").End(xlUp).Offset(1, 0)

this is A7, if the last populated cell is A6; A8, if the last populated cell is A7...

[mancubus]

ps: just saw you have merged cells.

i would use ThisCell = ThatCell rather than ThatCell. Copy / ThisCell.PasteSpecial Paste:=xlPasteAll as in post# 5

i personally never use merged cells for data entry.

[Mr_Mod]

Many thanks

[mancubus]

you're wellcome.

please mark the thread as "solved" from "Thread Tools" dropdown on top of the page.