Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple cells to another sheet
Ok here is what I need help with.
In sheet 1 I have a Invoice I would like to create a database of my customers So when I have wrote out the Invoice I would like to hit a command button that would send it to WS 2 next empty row. What would be the best way to do this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple cells to another sheet
I'm afraid it depends on how you have set out the structure of your invoice, would you want the information to appear in a single row on worksheet 2? feel free to join the forum in the signature below where you can attach a workbook to your post, if you do join, take note of this link: http://tinyurl.com/93xnpk and post in that thread with your workbook, that way all the people who have been follwing this thread here and wish to help will still be able to! ComputerMech;167242 Wrote: Ok here is what I need help with. In sheet 1 I have a Invoice I would like to create a database of my customers So when I have wrote out the Invoice I would like to hit a command button that would send it to WS 2 next empty row. What would be the best way to do this. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46344 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple cells to another sheet
Here is a sample of one method:
ActiveSheet.Selection.Copy Sheets(2).Range("A" & Range("A65536"). _ End(xlUp).Row + 1) Will put the selected range from the ActiveSheet into the next available row on sheet 2, assuming that column A of sheet 2 will be filled down as far as all other columns. "ComputerMech" wrote: Ok here is what I need help with. In sheet 1 I have a Invoice I would like to create a database of my customers So when I have wrote out the Invoice I would like to hit a command button that would send it to WS 2 next empty row. What would be the best way to do this. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple cells to another sheet
I have found the answer and have it working now thanks for all your help here
is the code that i ended up using Sub Copy_Next_Each_Other() Dim smallrng As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long Dim SourceRange As Range, I As Integer With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("Sheet2").Range("f5,f6,f7,f8,c10,a13,a16,j3 2,j33,j41,i45") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("Sheet6") Lr = LastRow(DestSheet) I = 1 For Each smallrng In SourceRange.Areas 'We make DestRange the same size as smallrng and use the 'Value property to give DestRange the same values With smallrng Set DestRange = DestSheet.Cells(Lr + 1, I) _ .Resize(.Rows.Count, .Columns.Count) End With DestRange.Value = smallrng.Value I = I + smallrng.Columns.Count Next smallrng With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function Function bIsBookOpen_RB(ByRef szBookName As String) As Boolean ' Rob Bovey On Error Resume Next bIsBookOpen_RB = Not (Application.Workbooks(szBookName) Is Nothing) End Function Please note that this is not my code but I have forgot the wed site to which it came if anyone does know where it came from please post "JLGWhiz" wrote: Here is a sample of one method: ActiveSheet.Selection.Copy Sheets(2).Range("A" & Range("A65536"). _ End(xlUp).Row + 1) Will put the selected range from the ActiveSheet into the next available row on sheet 2, assuming that column A of sheet 2 will be filled down as far as all other columns. "ComputerMech" wrote: Ok here is what I need help with. In sheet 1 I have a Invoice I would like to create a database of my customers So when I have wrote out the Invoice I would like to hit a command button that would send it to WS 2 next empty row. What would be the best way to do this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Sheet to new Sheet and clear cells on original sheets | Excel Discussion (Misc queries) | |||
copy cells from multiple pages to one sheet w/ if statement | Excel Worksheet Functions | |||
Formula to copy multiple cells onto another sheet | New Users to Excel | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
Copy sheet and clear cells on new sheet at sametime. | Excel Programming |