473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Copy specific rows to other sheet

Hi,

Sorry if I'm asking a super easy question. From sheet Details I need to copy next rows each time I click like for example - A5,B5,C5 to the next empty row in destination sheet. From Invoice I want to copy fixed rows in next avilable rows in the destination sheet. Below is the code that I have however it copies the fixed rows from Details sheet. Also it should paste values instead of formulae. Please help with this one.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()Application.ScreenUpdating = False
  2. Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
  3.     Dim DestRow As Long
  4.     Set ws1 = Sheets("Details")
  5.     Set ws2 = Sheets("Inv")
  6.     Set ws3 = Sheets("Reg")
  7.     DestRow = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
  8.     ws1.Range("A4").copy ws3.Range("A" & DestRow)
  9.     ws1.Range("B4").copy ws3.Range("D" & DestRow)
  10.     ws1.Range("C4").copy ws3.Range("G" & DestRow)
  11.     ws2.Range("B13").copy ws3.Range("N" & DestRow)
  12.     ws2.Range("H13").copy ws3.Range("L" & DestRow)
  13.     ws2.Range("I28").copy ws3.Range("J" & DestRow)
  14.     ws2.Range("H15").copy ws3.Range("K" & DestRow)
  15. Application.ScreenUpdating = True
  16. End Sub
  17.  
Feb 20 '15 #1
7 3293
Luuk
1,047 Expert 1GB
to paste valuess change:
Expand|Select|Wrap|Line Numbers
  1. ws1.Range("A4").Copy ws3.Range("A" & DestRow)
to:
Expand|Select|Wrap|Line Numbers
  1. ws1.Range("A4").Copy 
  2. ws3.Range("A" & DestRow).PasteSpecial xlPasteValues
Feb 21 '15 #2
Thanks Luuk, could you also answer two of my questions please

1. See below
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2. Application.ScreenUpdating = False
  3. Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
  4.     Dim DestRow As Long
  5.     On Error Resume Next
  6.     Set ws1 = Sheets("Customer")
  7.     Set ws2 = Sheets("Invoice")
  8.     Set ws3 = Sheets("Inv_Register_Commissions")
  9.     Set ws4 = Sheets("Inv")
  10.     DestRow = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
  11.     DestRow = ws4.Cells(Rows.Count, "A").End(xlUp).Row + 1
  12.     ws1.Range("A4").copy ------------------- I want to copy next row each time I click like A5, then A6
  13.     ws3.Range("A" & DestRow).PasteSpecial xlPasteValues
  14.     ws1.Range("B4").copy ------------------- I want to copy next row each time I click like B5, then B6
  15.     ws3.Range("D" & DestRow).PasteSpecial xlPasteValues
  16.     ws1.Range("C4").copy ------------------- I want to copy next row each time I click like C5, then C6
  17.     ws3.Range("G" & DestRow).PasteSpecial xlPasteValues
  18.     ws2.Range("B13").copy ------------------- However this is fixed row that i want to copy
  19.     ws3.Range("N" & DestRow).PasteSpecial xlPasteValues
  20.     ws2.Range("H13").copy ------------------- However this is fixed row that i want to copy
  21.     ws3.Range("L" & DestRow).PasteSpecial xlPasteValues
  22.     ws2.Range("I28").copy ------------------- However this is fixed row that i want to copy
  23.     ws3.Range("J" & DestRow).PasteSpecial xlPasteValues
  24.     ws2.Range("H15").copy ------------------- However this is fixed row that i want to copy
  25.     ws3.Range("K" & DestRow).PasteSpecial xlPasteValues
  26.     ws2.Range("B13").copy ------------------- However this is fixed row that i want to copy
  27.  Application.ScreenUpdating = True
  28. End Sub
  29.  
  30.  
2. From sheet "Inv" I want to copy specific rows and paste in specific rows based on one cell value. For example: If cell value = D/22/E then A2 will be pasted in H13 "DestSheet", B2 will be pasted in G4 "DestSheet" and C2 will be pasted in D33 "DestSheet". See the table below

Customer Name Address Phone # Ref#
A ABC 99999 A/99/A
B DEF 88888 D/22/E
C XYZ 77777 R/44/W
D UVW 66666 E/55/E


I know this can be done using the above formula but I don't know how to put the criteria of cell value.


Hope to get an answer soon.
Feb 21 '15 #3
Luuk
1,047 Expert 1GB
If you have an Excel file with this:
Expand|Select|Wrap|Line Numbers
  1.   a    b    c
  2.    1    2    3
  3.    4    5    6
  4.    7    8    9
  5.  
  6.  
(Cell "A2" contains: 1; Cell "C4" contains 9 )

Than the output of this formula:
Range("B1").End(XlDown).Row is 4
Because, if you start on "B1", go down to the End, you will be on row 4.

In your code:
Cells(rows.Count,"B").End(xlUp).Row
This starts at cell "B1048576" (because Rows.count=1048576) end goes up to the first cell which has content ("B4"), and returns its rownumber.

I hope this helps with your problem ;-)
Feb 22 '15 #4
Hi Luuk,

Thanks for the help but I'm so sorry I'm very new to vba coding and didn't really get to which question did you reply. Could you please answer me?
Feb 22 '15 #5
Luuk
1,047 Expert 1GB
Sorry for my "super easy answer" to your "super easy question"

I thought you could invest time in trying to solve the problem with some more effort from your site.

I don't have the time, to give more details, because:
I don't understand (fully) your problem (besides not knowing how to code in vba)
Feb 28 '15 #6
Thanks for your quick reply... I already solved my problem. Its' no use answering if you didn't really understand the problem. Thanks anyway...
Feb 28 '15 #7
twinnyfo
3,653 Expert Mod 2GB
LadyAlina,

Would you be willing to post your solution, just so others searching this forum might benefit from your efforts?
Mar 2 '15 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Devlei | last post by:
How do I copy the contents of selected rows in a DataGrid (that is bound to a Dataset via a DataView) to the Clipboard for pasting into other applications - such as Excel? Doing the same from a...
0
by: rshillington | last post by:
I have one content page that need a stylesheet that is in addition to the styles coded in the CSS for the theme. Since the content page doesn't have a HEAD element, how do I add a LINK to the...
1
by: Ambica Jain | last post by:
I want to change the backcolor of some specific rows in the DataGrid. I have seen some samples that do it by overriding Paint method of inherited column class. However, I am using typed dataset...
5
by: Mahesh S | last post by:
Hi I would like to write a SQL select statement that would fetch rows numbered 50 to 100. Let me clarify, if i say "fetch first 10 rows only", it returns the first 10 rows from the resultset....
0
by: ciao | last post by:
Hi, I have a list of data stored in a multidimensional table (5 rows, 12 lolumns). I need to find the min value of each row, so i want to store the contents of each row to a "simple" table (so...
0
by: slenish | last post by:
Hello, Ok I am having a problem where I am taking an Excel File and cutting and pasting the info from the first sheet into an Email on a button click on a form. I actually have it working but...
5
by: scrapcode | last post by:
Hi everyone. I'm trying to write a macro to copy data from one Excel sheet to another in the same workbook when a certain criteria is met. My data table is an import from an Access DB. After the...
2
by: betty blue | last post by:
I am a newbie to C#. I want to select three columns from my text file i.e. Empl No, Start Date and Created Date. After selecting this, I want to insert the data into a database. I have attached a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.