PDA

View Full Version : Copy and Paste issues



bungay
03-06-2006, 12:41 PM
I am fairly new to vba and I have managed to make a userform for data entry it to sheet1 and I would like to copy the row of information to one of the 4 other sheets labeled cls; cms; cas based on the data inputed into column B of sheet1. I need the data to stay in sheet1 but copy to the necessary sheet. I managed to do this once, but it would not copy to more than one sheet. Any help would be appreciated!:banghead:

Ken Puls
03-06-2006, 12:45 PM
Hi bungay, and welcome to VBAX!

Can you post the code you are using? Or maybe a copy of the spreadsheet with sanitised data?

I just want to make sure your userform is a userform, and not just a data entry sheet. :)

bungay
03-06-2006, 03:12 PM
I am fairly new to vba and I have managed to make a userform for data entry it to sheet1 and I would like to copy the row of information to one of the 4 other sheets labeled cls; cms; cas based on the data inputed into column B of sheet1. I need the data to stay in sheet1 but copy to the necessary sheet. I managed to do this once, but it would not copy to more than one sheet. Any help would be appreciated!:banghead:
Private Sub CopyRows()
Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column B
ThisValue = Range("B" & x).Value
If ThisValue = "CMS" Then
Range("A" & x & ":K" & x).Copy
Sheets("CMS").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Sheets("Sheet1").Select

End If

Next x
Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column B
ThisValue = Range("B" & x).Value




i have used the userform with other apps before so I know that it works

XLGibbs
03-06-2006, 04:52 PM
Private Sub CopyRows()
With Sheets("Sheet1")
' Find the last row of data
FinalRow = .Range("A65536").End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column B

ThisValue = .Range("B" & x).Value

.Range("A" & x & ":K" & x).Copy

With Sheets(ThisValue).
NextRow = .Range("A65536").End(xlUp).Row + 1
.Range("A" & NextRow).PasteSpecial (xlValues)
End With

End With

Next x

Try this ....

same code, more efficient. Identifies the paste sheet via the value in Range("B" & x) as your original code did.

Try not to use Select as this slows down your code.

By containing the necessary ranges inside a With statement, the object reference will stay, and the paste range is inside another With...End With statement for the paste operation...

bungay
03-06-2006, 05:13 PM
this removes the target values I need to paste based on ie. "cms"

with this change how do I set it for 4-6 sheets based on other "words"

XLGibbs
03-06-2006, 08:26 PM
The code indicates that it copies columns A:K for each row in the range on "Sheet1" and pastes it to a sheet identified by name/text in column B of that row. If you identify the sheet name of your desired sheets in column B, it would work. It does not remove the value from column B, or at least it shouldn't based on the way it is written. So if you change the words in column B to your sheet names, it should work.

bungay
03-07-2006, 03:33 AM
thanks I will give it a try

Ken Puls
03-07-2006, 10:37 PM
Pete,

Thanks for picking up for me. A long day away from the forums for me... :)

XLGibbs
03-08-2006, 05:18 PM
No worries Ken. I know it has been a rough time lately,and noticed the time since your post..figured you might have been otherwise indisposed.