Copy paste data in Excel using VBA

How to copy paste data in Excel using VBA in different ways like selecting contiguous and non-contiguous cells or transposing pasted data.

[Radovan]

Mr. Takyar,
great excel lessons. Thank you

Radovan

[angelaskipping]

Hello Mr. Takyar. I really appreciate the simplicity of your videos. I have learned a lot about VBA coding when it is contained to sheets or contained to tables. I, however, am needing to combine the two with my project. On Sheet 1 I have a worksheet where the first column is a date, 2nd column is a number, 3rd column is a combination of title names and descriptions, and 4th and 5th columns are numbers. I need to copy the 1st, 4th, and 5th entries on each row that contains a number in the 2nd column into a list object that is found on sheet 2 based on the number in column 2 on sheet 1. (Some of the cells in column 2 are blank, because there are no numbers in front of the descriptions in column 3, and there are multiple list objects on sheet 2.) I tried to select case based on account number, but I can only get this to work if the data is not transferring to a list object. I tried to just copy and assigned as I've learned from your video where you effectively paste non-contiguous cells adjacently from sheet 1 to sheet 2, but I get stuck, because I'm not just pasting from sheet to sheet, but from sheet to a list object where the properties appear to not work the same.

[AniManuSCh]

How can I do that, but with no fixed cells, I mean for example, copy some data, but paste it on a cell below some other cell, so the list could increment instead of overwirte

[StationOnLife]

Thank you for a wonderful tutorial. I have a question. I want to copy a cell that contain formula and paste it as value (for example: a cel contain formula like sum(a1:10) which have result of 10. Therefore, I want to be able copy the cell and paste as value.). Thank you.

[hemantapte]

Dinesh sir,
I went through your video on vba copypaste and I found it very well explained. I have a small difficulty as I am a beginner for vba. I want to copy a part of text from one cell and append it to the text in another cell. How to go about this?
Pl help.

[muhammadshahbaz]

Can we paste into different cells together for example we can pate paste values in 1st two columns then skip third and fourth column then paste it on 5th column is that possible with the vba code if u have plz explain

[wendyn]

Hi, Dinesh. Thank you for giving me the following code to mark the training times to a different workbook.

Private Sub Worksheet_SelectionChange (ByValTarget As Range)
Dim i As Long
lastrow=Sheet1.Cells(Rows.Count, 1).End(xlsUp).Row
For i=2 To lastrow
If Cells (i, 1)=33
Then Sheet2.Cells(i, 33)="X"
End If
Next End Sub

For long term usage, I would have many other numeric (from 1-99) to be entered under "training times".

How can I make all the "training times" I entered, automatically mark an "X" under the corresponding number ?

E.g. "33" on workbook (1) mark an "X" under "33" column on workbook (2)
E.g. "45" on workbook (1) mark an "X" under "45" column on workbook (2)

Thank you for your help in advance.

[djm]

Mr. Takyar,
Can Excel calculate the total minutes passed between 2 dates and times, such as 1/1/16 13:30 to 1/5/16 4:30? Thank you for your help? MD

[MarkGomesdotin]

Hi Dinesh, Thank you for sharing.
I have the below question request your assistance.
I have selected a range in Excel and i need to paste this selection as "unicode Text" in

[DBCreations]

Hi Dinesh. I am learning from your videos and really appreciate for sharing. I have a doubt to clarify please. During transpose, how to repeat column headers to row level and row headers as it is with cell data onto a new sheet ?

[musininagaraju]

Dear Sir,
Thank you very much for providing valuable knowledge. I need a vba code to copy data from two worksheets and paste in another worksheet. For example I have two work sheets in one sheet Bank Payments and in another sheet Bank receipts. now I need to mix both work sheets in one another sheet and sort automatically by date and show daily balance.
thanks and regards
Nagaraju M

[burakege]

Hi, this formula copy and paste selected cells formulas. I need only copy and paste selected cells’ value. Can you help me?

[khans]

I want to copy specific data from sheet1 to sheet3 for example.i want to copy the data which contains word special from sheet1 to sheet3.please assist me on this.

[enochjf]

dinesh sir good. one question how do I copy row ranges with specific values. do we have declare variables and use step function. can you help please. this would be a piece of cake...can
u break it down tp the most elementary level..please

[LearnerCB]

Sir,

I followed your instructions in the other video about using STEP IN, after after carefully watching each step, I moved a few things that seemed out of step in the process of creating, writing, and saving, closing the files as needed.

If you notice, I moved the command for saving the final PDF file to the very end.

Thank you

Below is the working solution:

Sub NextInvoice()
'H10 is the invoice number
'H12 in the customer ID
Application.DisplayAlerts = False
Range("H10").Value = Range("H10").Value + 1

Range("D37").NumberFormat = ("dd mmm yyyy")
End Sub
Sub SaveInvWithNewName()
Application.DisplayAlerts = False
Dim DC_date As Date
Dim myFile As String, lastRow As Long
Dim myFile1 As String, lastRow1 As Long
'Copy Invoice to a new workbook
'Create [Proforma Invoice]PFI*.xlsx
'Create [Proforma Invoice]PFI*.PDF
'Create [Receipt]RE00*.xlsx
'Create [Receipt]RE00*.PDF

ActiveSheet.Copy
NewFN = "T:\Invoices\Customer Invoices\AD 895417\" & Range("H12") & "_PFI00" & Range("H10").Value & ".xlsx"
ActiveSheet.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
myFile = "T:\Invoices\Customer Invoices\AD 895417\" & Range("H12") & "_PFI00" & Range("H10").Value & ".pdf"
Type:=xlTypePDF, Filename:=myFile

NewFN = "T:\Invoices\Customer Invoices\AD 895417\" & Range("H12") & "_RE00" & Range("H10").Value & ".xlsx"
ActiveSheet.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
value_1 =
value_2 =
value_3 =

ActiveSheet.Range("A7") = value_1
ActiveSheet.Range("A37") = value_2
ActiveSheet.Range("D37") = value_3
Type:=xlTypePDF, Filename:= _
"T:\Invoices\Customer Invoices\AD 895417\" & Range("H12") & "_RE00" & Range("H10").Value & ".pdf"
ActiveWorkbook.Save
ActiveWorkbook.Close
NextInvoice
End Sub

[mout]

Hi does anybody know why I still get the msg "Do you want to save the changes"?See the code below, can somebody see what is wrong?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
= False                  [BSC!AE2] = Now()    [BSC!AC2] = Author") & " at: " & Save Time")        Application.DisplayAlerts = False    ActiveWindow.Zoom = 85    Range("AD6:AD9").Select    Selection.ClearContents    ActiveWindow.SmallScroll ToRight:=-80    = True    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, = TrueEnd Sub