-
Sep 24th, 2017, 08:26 AM
#41
Re: Save data from one workbook to another book
from post #37
add this at the end of the existing code
as part of the previously existing code is no longer there targrange is now invalid
I need to update next sheet also.
so it appeared you still needed all the previous code
also you need dim on line 4
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Sep 24th, 2017, 11:10 PM
#42
Thread Starter
Lively Member
Re: Save data from one workbook to another book
Still I'm confused... can you please give the code..
I need to update the data to next sheet in same workbook at the same time it should be update to workbook B.
You already give code to update on workbook B that's working fine...
Un that same code i want add this steps..so that when i click the button code to find the Network no. For next sheet and paste as well as paste to workbook B.
-
Sep 25th, 2017, 05:10 AM
#43
Re: Save data from one workbook to another book
try like
Code:
Sub Update()
Dim p As String, targrange As Range, wbb As Workbook, fnd As Range, sht2 as worksheet
Set wbb = Workbooks.Open("C:\\Users\Dinesh\dashboard\B.xlsm") ' change to suit
Set targrange = wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1) ' targrange would be B and first empty row on the first worksheet
targrange.Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("c5:c14").Value)
targrange.Offset(, 10).Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("e5:e14").Value)
Set sht2 = thisworkbook.sheets("sheet2")
set netnum = thisworkbook.sheets("Portal").range("c5") ' change if i got this wrong
Set fnd = sht2.Range("b:b").Find(netnum)
if fnd is nothing then ' if network number not found
msgbox netnum & " not found, do manually": exit sub
end if
fnd.Resize(, 20).Value = targrange.Resize(, 20).Value ' change the resize it these are incorrect
wbb.Close True ' save changes
End Sub
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Sep 25th, 2017, 07:05 AM
#44
Thread Starter
Lively Member
[SOLED] Save data from one workbook to another book
Thank you so much Westconn
This is working Perfect.
Thank you
-
Oct 3rd, 2017, 04:03 PM
#45
Thread Starter
Lively Member
Re: Save data from one workbook to another book
Hi westconn
Sorry for disturb again.
I have make small changes on this code,as per changes it should be find the network no. on backups sheet if Network no is there it should be replace the data if the network no is not there this should be paste in last blank row.
Code:
Sub Update()
Dim p As String, targrange As Range, wbb As Workbook, fnd As Range, sht2 As Worksheet
Set wbb = Workbooks.Open("C:\\Users\Dinesh\dashboard\B.xlsm") ' change to suit
Set targrange = wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1) ' targrange would be B and first empty row on the first worksheet
targrange.Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("c5:c14").Value)
targrange.Offset(, 10).Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("e5:e14").Value)
'targrange.Offset(, 12).Value = ThisWorkbook.Sheets(1).Range("c11").Value
Set sht2 = ThisWorkbook.Sheets("Backups")
Set netnum = ThisWorkbook.Sheets("Portal").Range("c5") ' change if i got this wrong
Set fnd = sht2.Range("a:a").Find(netnum)
If fnd Is Nothing Then ' if network number not found
sht2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues: Exit Sub
End If
fnd.Resize(, 20).Value = targrange.Resize(, 20).Value ' change the resize it these are incorrect
wbb.Close True ' save changes
End Sub
But when i try to run this code getting error like..
Pastespecial Method of Range class failed
Please help me how to solve this.
Thanks
-
Oct 4th, 2017, 04:50 AM
#46
Re: Save data from one workbook to another book
the paste failed as the data had never been "copied"
just assign the values to the resized range
i would change like
Code:
If fnd Is Nothing Then ' if network number not found
set fnd = sht2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
the following line should then copy the data to the correct location regardless
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 4th, 2017, 04:06 PM
#47
Thread Starter
Lively Member
Re: Save data from one workbook to another book
Thank you so much ...It was working perfect
-
Oct 7th, 2017, 02:02 PM
#48
Thread Starter
Lively Member
Re: Save data from one workbook to another book
Hi Westconn
I need help again.....
Actually this working good...but i want add one more option on it.. current code copy the data and find the Network number on sheet 2(Backups) if Network number already there it will replace the data or Network number not found it will paste in new row.. What i want to add in this code is before paste the sheet 2 it will find the duplicate network number if duplicate number is there then it will show a message, if duplicate number is not there then it will paste as per current code..( because in sheet 2 some duplicate network number is there)
Code:
Sub Update()
Dim p As String, targrange As Range, wbb As Workbook, fnd As Range, sht2 As Worksheet
Set wbb = Workbooks.Open("C:\\Users\Dinesh\dashboard\B.xlsm") ' change to suit
Set targrange = wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1) ' targrange would be B and first empty row on the first worksheet
targrange.Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("c5:c14").Value)
targrange.Offset(, 10).Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("e5:e14").Value)
'targrange.Offset(, 12).Value = ThisWorkbook.Sheets(1).Range("c11").Value
Set sht2 = ThisWorkbook.Sheets("Backups")
Set netnum = ThisWorkbook.Sheets("Portal").Range("c5") ' change if i got this wrong
Set fnd = sht2.Range("a:a").Find(netnum)
If fnd Is Nothing Then ' if network number not found
sht2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues: Exit Sub
End If
fnd.Resize(, 20).Value = targrange.Resize(, 20).Value ' change the resize it these are incorrect
wbb.Close True ' save changes
End Sub
Thanks
-
Oct 7th, 2017, 04:02 PM
#49
Re: Save data from one workbook to another book
try like
Code:
If Application.WorksheetFunction.CountIf(sht2.Range("a:a"), netnum) > 1 Then MsgBox "duplicates exist"
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 7th, 2017, 06:12 PM
#50
Thread Starter
Lively Member
Re: Save data from one workbook to another book
Excellent , thanks for your Quick reply....its working fine....
Thank you so much
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
|