Move Only The Selected Data From 1 WorkBook sheet1 to Another Already Workbook

[xcruc1at3r]

Can any one Kindly Plssss...Modify this Coding To The Selected Data From 1 WorkBook's Sheet1 To Another Already Created WorkBook's Sheet1 in My documents.

Will Appreciate the HELP


Sub CopySelectedMulti()

Dim NextRow&, rng As Range

With Sheets("Sheet2")

For Each rng In Selection.Areas

On Error Resume Next

NextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

If Err <> 0 Then NextRow = 1

On Error GoTo 0

rng.Copy .Cells(NextRow, 1)

Next rng

End With

End Sub

 

[Luke M]

I'd recommend starting here:

http://www.rondebruin.nl/copy5.htm


and if that's not exactly it, Ron has several pages that deal with various copying/merging examples (scroll down to "Copy/Paste/Merge examples"):

http://www.rondebruin.nl/tips.htm

 

[xcruc1at3r]

well it went over my head bro am not good with VBA at all... that why if anyone can kindly modify it. cause i want the only selected data from A.xls>sheet1 in B.xls sheet1 the b.xls already have been created...it a existing xls workbook.

 

[Luke M]

How's this? Copies sheet1 from current workbook to sheet1 of Book2.xls

[pre]

Sub CopyASheet()

'Change this address as needed
Workbooks.Open Filename:= _
"C:My DocumentsBook2.xls"

'First line is where you are copying from
ThisWorkbook.Worksheets("Sheet1").Cells.Copy _
ActiveWorkbook.Worksheets("Sheet1").Cells 'This line is where you're copying to

Application.CutCopyMode = False
End Sub

[/pre]

 

[xcruc1at3r]

WELL LUKE Thanks for your concern my friend but I just need to Move the ONLY Selected Range "For Each rng In Selection.Areas" to

Workbooks.Open Filename:= _

"C:My DocumentsBook2.xls" ("Sheet1")

NextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1


the purpose is that i have a daily report we add daily activities is the daily report and we have to move that daily activity only to the database report so we can manage the main database. if we want to check what was the total activity of dd/mm/yy so we can check the database report.

that why we just need to move the selected range from the daily report to the Main database report's sheet1

 

[xcruc1at3r]

i know Luke U can do it because you have the knowledge

 

[Luke M]

I'm having trouble understanding what exactly you want to copy. Are you selecting a range first, or do you want the macro to figure it out? This should be pretty close. I went with your first example where you're looping through different ranges.

[pre]

Sub CopyASheet()
Dim rng As Range, CopyArea As Range
Dim NextRow As Integer

'What exactly are you wanting to copy??
For Each CopyArea In Selection.Areas
Set rng = CopyArea

'Change this address as needed
Workbooks.Open Filename:= _
"C:My DocumentsBook1.xls"
ActiveWorkbook.Worksheets("Sheet1").Select

With ActiveSheet
On Error Resume Next
NextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
If Err <> 0 Then NextRow = 1
On Error GoTo 0

rng.Copy .Cells(NextRow, 1)
End With
Next
Application.CutCopyMode = False
End Sub

[/pre]

 

[xcruc1at3r]

LUKE CAN YOU CHECK THIS CODING OUT ITS SHOWING ERROR IN THE END...WITH "End Sub"

AND DOES NOT SHOW THE ENDING MSG AS WELL ""Data transfer complete.""

Sub MoveStuff()

Dim sourceBook As Workbook

Dim targetBook As Workbook

Dim targetPath As String

'make easy reference names for both the book data is coming FROM and going TO

Set sourceBook = ActiveWorkbook

'set the target workbook's filepath as a variable so it is easy to change later

targetPath = “siggen01longworth$insurance sheet.xlsx”

'copy the selected range

Selection.Copy

'open the target book

Workbooks.Open targetPath, Format:=2

'make easy reference names for both the book data is coming FROM and going TO

Set targetBook = ActiveWorkbook

'paste info into A1 on sheet 1

targetBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial.Active

ActiveSheet.Paste

targetBook.Activate

MsgBox "Data transfer complete.", vbInformation, "Process Complete"

End Sub

 

[Luke M]

Looks like the PasteSpecial had the wrong object called out. I made the assumption that you wanted to just paste values:

[pre]

Sub MoveStuff()
Dim sourceBook As Workbook
Dim targetBook As Workbook
Dim targetPath As String
'make easy reference names for both the book data is coming FROM and going TO
Set sourceBook = ActiveWorkbook
'set the target workbook's filepath as a variable so it is easy to change later
targetPath = "siggen01longworth$insurance sheet.xlsx"
'copy the selected range
Selection.Copy
'open the target book
Workbooks.Open targetPath, Format:=2
'make easy reference names for both the book data is coming FROM and going TO
Set targetBook = ActiveWorkbook
'paste info into A1 on sheet 1
targetBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ActiveSheet.Paste
targetBook.Activate
MsgBox "Data transfer complete.", vbInformation, "Process Complete"
End Sub

[/pre]

 

[xcruc1at3r]

BinDOOOOo... Thanks Alot Luke THE EXCEL NINJAAAAAAAAAAAAAAAAAA HIGH FIVE...

 

[Luke M]

You're very welcome. =)

 

[xcruc1at3r]

Luke Bro THis Coding is lacking at 1 stage.. when the Database.xls is Active. it says do u want to reopen the File the data will be lost....

so if perfect for the closed file can u add the option if the Database.xls is active so paste the selected range

 

[Luke M]

How about this? Breaks the targetpath into two parts, but allows you to check if the workbook is already opened.

[pre]

Sub MoveStuff()
Dim sourceBook As Workbook
Dim targetBook As Workbook
Dim targetPath As String
Dim BookName As String
Dim NeedOpen As Boolean

'make easy reference names for both the book data is coming FROM and going TO
Set sourceBook = ActiveWorkbook
'set the target workbook's filepath as a variable so it is easy to change later
targetPath = "siggen01longworth$"
BookName = "Database.xls"
'copy the selected range
Selection.Copy

'Check if workbook is already opened
NeedOpen = True
For Each wb In Application.Workbooks
If wb.Name = BookName Then
NeedOpen = False
Exit For
End If
Next

If NeedOpen Then
'open the target book
Workbooks.Open targetPath, Format:=2
Else
Workbooks(BookName).Activate
End If

'make easy reference names for both the book data is coming FROM and going TO
Set targetBook = ActiveWorkbook

'paste info into A1 on sheet 1
targetBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ActiveSheet.Paste
targetBook.Activate
MsgBox "Data transfer complete.", vbInformation, "Process Complete"
End Sub

[/pre]

 

[xcruc1at3r]

WORKS LIKE A CHARM THANKS AGAIN "THE EXCEL NINJA"

 

[xcruc1at3r]

Luke bro.. The coding that u created is not working for the sheet when its open!...it says

the Excel Sheet is already Open. Reopening will cause any changes you made to be discarded...

and is not working on multiple selection...

if the file is already open so its should Transfer the data in sheet 1 the 1st empty cell rite..

 

[xcruc1at3r]

LUKE need help with this coding ...

 

[Luke M]

Change the one block of coding to this:

[pre]

Application.DisplayAlerts = False
If NeedOpen Then
'open the target book
Workbooks.Open targetPath, Format:=2
Else
Workbooks(BookName).Activate
End If
Application.DisplayAlerts = True

[/pre]
That should hide the open error message.

 

[xcruc1at3r]

Okay buddy...let me give it a short

 

[xcruc1at3r]

given error at

'paste info into A1 on sheet 1

targetBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


after changing the coding from :

Next

If NeedOpen Then

'open the target book

Workbooks.Open targetPath, Format:=2

Else

Workbooks(BookName).Activate

End If


TO the coding :

Next


Application.DisplayAlerts = False

If NeedOpen Then

'open the target book

Workbooks.Open targetPath, Format:=2

Else

Workbooks(BookName).Activate

End If

Application.DisplayAlerts = True

 

[Luke M]

Hmm. Looks like I forgot to include a line stating what you wanted to copy. Is this a given range, or did you just want to use whatever was selected when macro started?

[pre]

Sub MoveStuff()
Dim sourceBook As Workbook
Dim targetBook As Workbook
Dim targetPath As String
Dim BookName As String
Dim NeedOpen As Boolean

'make easy reference names for both the book data is coming FROM and going TO
Set sourceBook = ActiveWorkbook
'set the target workbook's filepath as a variable so it is easy to change later
targetPath = "siggen01longworth$"
BookName = "Database.xls"
'copy the selected range
Selection.Copy

'Check if workbook is already opened
NeedOpen = True
For Each wb In Application.Workbooks
If wb.Name = BookName Then
NeedOpen = False
Exit For
End If
Next

'~~~~~Beginning of edit, next line may need to be changed
Selection.Copy

If NeedOpen Then
'open the target book
Workbooks.Open targetPath & BookName, Format:=2 'Forgot to include BookName :(
Else
Workbooks(BookName).Activate
End If

'make easy reference names for both the book data is coming FROM and going TO
Set targetBook = ActiveWorkbook

'paste info into A1 on sheet 1
targetBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'~~~~~~End of edit

MsgBox "Data transfer complete.", vbInformation, "Process Complete"
End Sub

[/pre]

 

[xcruc1at3r]

yes what ever rang is selected at the time of macro started....

 

[xcruc1at3r]

and should move it to the empty row found in the target sheet..

 

[xcruc1at3r]

AwSome.. its works with the multiple selection and even if the Sheet is active its transfers the Data....This Thread IS COMpleteddd )))...yUppiieeeeeee

THanks a Zillion Luke U are the MAN!!..

 

[xcruc1at3r]

((((....Sorry bro...am bugging u again Luke (....Its not able to find the target

showing error at this coding:

"Workbooks.Open targetPath & BookName, Format:=2 'Forgot to include BookName "

but when the Sheet is opened so its Moving the Data successfully ....

 

[Luke M]

Check the targetPath and BookName definitions, make sure they are both correct. No missing slash marks, the ".xls" is there?