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. -
Private Sub CommandButton1_Click()Application.ScreenUpdating = False
-
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
-
Dim DestRow As Long
-
Set ws1 = Sheets("Details")
-
Set ws2 = Sheets("Inv")
-
Set ws3 = Sheets("Reg")
-
DestRow = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
-
ws1.Range("A4").copy ws3.Range("A" & DestRow)
-
ws1.Range("B4").copy ws3.Range("D" & DestRow)
-
ws1.Range("C4").copy ws3.Range("G" & DestRow)
-
ws2.Range("B13").copy ws3.Range("N" & DestRow)
-
ws2.Range("H13").copy ws3.Range("L" & DestRow)
-
ws2.Range("I28").copy ws3.Range("J" & DestRow)
-
ws2.Range("H15").copy ws3.Range("K" & DestRow)
-
Application.ScreenUpdating = True
-
End Sub
-
7 3293 Luuk 1,047
Expert 1GB
to paste valuess change: - ws1.Range("A4").Copy ws3.Range("A" & DestRow)
to: - ws1.Range("A4").Copy
-
ws3.Range("A" & DestRow).PasteSpecial xlPasteValues
Thanks Luuk, could you also answer two of my questions please
1. See below - Private Sub CommandButton1_Click()
-
Application.ScreenUpdating = False
-
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
-
Dim DestRow As Long
-
On Error Resume Next
-
Set ws1 = Sheets("Customer")
-
Set ws2 = Sheets("Invoice")
-
Set ws3 = Sheets("Inv_Register_Commissions")
-
Set ws4 = Sheets("Inv")
-
DestRow = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
-
DestRow = ws4.Cells(Rows.Count, "A").End(xlUp).Row + 1
-
ws1.Range("A4").copy ------------------- I want to copy next row each time I click like A5, then A6
-
ws3.Range("A" & DestRow).PasteSpecial xlPasteValues
-
ws1.Range("B4").copy ------------------- I want to copy next row each time I click like B5, then B6
-
ws3.Range("D" & DestRow).PasteSpecial xlPasteValues
-
ws1.Range("C4").copy ------------------- I want to copy next row each time I click like C5, then C6
-
ws3.Range("G" & DestRow).PasteSpecial xlPasteValues
-
ws2.Range("B13").copy ------------------- However this is fixed row that i want to copy
-
ws3.Range("N" & DestRow).PasteSpecial xlPasteValues
-
ws2.Range("H13").copy ------------------- However this is fixed row that i want to copy
-
ws3.Range("L" & DestRow).PasteSpecial xlPasteValues
-
ws2.Range("I28").copy ------------------- However this is fixed row that i want to copy
-
ws3.Range("J" & DestRow).PasteSpecial xlPasteValues
-
ws2.Range("H15").copy ------------------- However this is fixed row that i want to copy
-
ws3.Range("K" & DestRow).PasteSpecial xlPasteValues
-
ws2.Range("B13").copy ------------------- However this is fixed row that i want to copy
-
Application.ScreenUpdating = True
-
End Sub
-
-
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.
Luuk 1,047
Expert 1GB
If you have an Excel file with this:
(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 ;-)
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?
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)
Thanks for your quick reply... I already solved my problem. Its' no use answering if you didn't really understand the problem. Thanks anyway...
LadyAlina,
Would you be willing to post your solution, just so others searching this forum might benefit from your efforts?
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |