Bottom line: Learn 3 different ways to copy and paste cells or ranges in Excel with VBA Macros. This is a 3-part video series and you can also download the file that contains the code.
Skill level: Beginner
Copy & Paste: The Most Common Excel Action
Copy and paste is probably one of the most common actions you take in Excel. It's also one of the most common tasks we automate when writing macros.
There are a few different ways to accomplish this task, and the macro recorder doesn't always give you the most efficient VBA code.
In the following three videos I explain:
- The most efficient method for a simple copy and paste in VBA.
- The easiest way to paste values.
- How to use the PasteSpecial method for other paste types.
You can download the file I use in these videos below. The code is also available at the bottom of the page.
Video #1: The Simple Copy Paste Method
You can watch the playlist that includes all 3 videos at the top of this page.
Video #2: An Easy Way to Paste Values
Video #3: The PasteSpecial Method Explained
VBA Code for the Copy & Paste Methods
Download the workbook that contains the code.
'3 Methods to Copy & Paste with VBA
'Source: https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/
'Author: Jon Acampora
Sub Range_Copy_Examples()
'Use the Range.Copy method for a simple copy/paste
'The Range.Copy Method - Copy & Paste with 1 line
Range("A1").Copy Range("C1")
Range("A1:A3").Copy Range("D1:D3")
Range("A1:A3").Copy Range("D1")
'Range.Copy to other worksheets
Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")
'Range.Copy to other workbooks
Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _
Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")
End Sub
Sub Paste_Values_Examples()
'Set the cells' values equal to another to paste values
'Set a cell's value equal to another cell's value
Range("C1").Value = Range("A1").Value
Range("D1:D3").Value = Range("A1:A3").Value
'Set values between worksheets
Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value
'Set values between workbooks
Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = _
Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value
End Sub
Sub PasteSpecial_Examples()
'Use the Range.PasteSpecial method for other paste types
'Copy and PasteSpecial a Range
Range("A1").Copy
Range("A3").PasteSpecial Paste:=xlPasteFormats
'Copy and PasteSpecial a between worksheets
Worksheets("Sheet1").Range("A2").Copy
Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas
'Copy and PasteSpecial between workbooks
Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy
Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats
'Disable marching ants around copied range
Application.CutCopyMode = False
End Sub
Paste Data Below the Last Used Row
One of the most common questions I get about copying and pasting with VBA is, how do I paste to the bottom of a range that is constantly changing? I first want to find the last row of data, then copy & paste below it.
To answer this question, I created a free training video on how to paste data below the last used row in a sheet with VBA. Can I send you the video? Please click the image below to get the video.
Free Training on Macros & VBA
The 3 videos above are from my VBA Pro Course. If you want to learn more about macros and VBA then checkout my free 3-part video training series.
I will also send you info on the VBA Pro Course, that will take you from beginner to expert. Click the link below to get instant access.
Please leave a comment below with any questions. Thanks!
Hi
I copied and pasted some data from sheet1 to sheet2 using VBA code.but the formula not calculating value with VBA pasted (Sheet2) Data please solve my problem.
Thanks.
good afternoon ,please could you support to us in the following , I have a question , we have a range for example A1:D300 and we have some cells and the condition is “force” word , for example we could have it into some cells like D150 , D200 , D100 they have the word force , and if it is true , all the line and the contents of the cells A100 , B100, C100 and D100 ; A150 , B150 , C150 , D150 and A200, B200 , C200 and D200 those data should copied to another range
Thanks for your support
Ricardo
Hi Ricardo,
I’m not sure I fully understand your question, but you might want to try filtering the range for the criteria, then copying the visible cells only. I hope that helps.
I have a worksheet with live data of a currency value relative to a USD that updates every 10 minutes in cell e2, and a live clock in cell d2. I would like to record both of these values every 10 minutes in another worksheet, so I have an account of the daily volatility of the value. What is the best way to do this?
Hi Sandip,
There are probably a few ways to do this. One way is to use the Application.Wait method in a loop. Set the wait for 10 minutes within a loop that repeatedly copies and pastes the data to a new sheet. The macro will have to be running all the time though. What is the process for updating the data every 10 minutes now? Is it a macro?
Hi Jon,
I am a beginner and started learning macros a few days ago.
I have created a Quality Form for my team. What I am trying to do is, Every time I audit a call, score someone and click on “Save”, all his scores should get save into Sheet 2. Next time when I audit another call, its data should save right below the previous call’s data on Sheet 2. So that I can calculate the average quality scores of all the agents and the team at one place.
I hope I was able to explain myself.
Could you please help!
Thanks
Umesh
Hi,
I tried using the same codes for Excel 2016 in Mac, sadly it doesn’t work. May I know is there a difference in coding VBA in Mac and Windows? I am new to VBA and urgently need help to create a macro for a project.
Many thanks.
Hi Vanessa,
Unfortunately, Excel 2016 for Mac does not contain a solid VB Editor for development and debugging code yet. It’s very difficult, if not impossible, to write macros on the Mac 2016 version of Excel. Microsoft is working on updating it. You can cast your vote for this feature on the Excel User Voice site. Here is the link to the post about VBA for Mac where you can vote.
Dear Jon;
I am an experienced programmer with over 30 years experience, including VB6, VB.NET, etc. I am in a new job where report data is generated in SQL, then put into hidden Worksheets. At that point Excel VBA code kicks in to copy the raw data into formatted worksheets. Platform is 64 bit Windows 10, 32 bit Office 365.
I am building a new report and am having the WORST time trying to copy and paste between worksheets! I get told “I can’t do that to a merged cell” when a cell is not merged, I get “Do you want to replace existing data?” when the cells are empty. Most infuriating, I define a SourceRange object of type Range and set it’s extent, but it doesn’t take! I see it on the spreadsheet, it looks right, but the .Rows.Count and Columns.Count values are wrong!
Is this stuff buggy beyond belief or am I missing something?
I’m not asking for a specific solution, just your general impressions. Is this platform messing with my head? I’m going nuts!
Thanks,
Barry
Hi Barry,
Sorry to hear that. It sounds frustrating. I have not heard or experienced bugs like that. I typically unmerge all cells in a sheet first when doing copy/paste with VBA. Merged cells are difficult to work with in VBA and can cause problems. You might want to try unmerging all the cells on the sheets to see if that helps solve the issues. I can also refer you to a consultant to take a look at your project if you’d like. I hope that helps.
Dear Jon,
Thanks for explaining this along with illustrative videos. On similar lines, can you please suggest VBA for copying text from a cell and pasting + searching that in google search tab.
For example, copying text string from Row 2 (D2, E2, F2, and so on) and pasting + searching the text strings in separate tabs in a browser window (www.google.com).
Thanks in advance.
Hi,
I am new to VBA. Kindly let me know how write a macro that will paste selected data from one cell into another cell in the same worksheet.
Hi Jon,
Currently i am developing a macro tool for my work. i want to copy a no:of column values according to the user input numbers and paste it on an other sheet.
Can u help me for this.
Thanks
Shamna H
Hi Jon,
I was watching the above videos (https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/ ) and the first 2 videos are Identical. In fact, they’re both same duration as well as saying “1 of 3”. Is there a 2nd video to see? I would like to see it (perhaps it’s a bad link (going to the 1st video?)?)
Thank you
I refreshed page and all was well. Thanks
Hi
i have tried to use the following for copy paste. doesn’t work. Can you plz help.
Sub PasteSpecial_Examples()
‘Copy and PasteSpecial between workbooks
Workbooks(“LH_MONTHLY_20170131_No_MA_TEST.xlsm”).Worksheets(“CFR (Treasury)”).Range(“A1”).Copy
Workbooks(“LH_MONTHLY_20170131_V1_TEST.xlsm”).Worksheets(“CFR (Treasury)”).Range(“A1”).PasteSpecial Paste:=xlPasteFormats
‘Disable marching ants around copied range
Application.CutCopyMode = False
End Sub
Hi Jon,
I am attempting to copy a range of values from a worksheet called ‘import’, to a worksheet called ‘data’. The row of these two worksheets will change, so I can’t use the Range(“D2:E2”) function, I need to use the cells function. The macro works when I use the Range function, but is doesn’t work when I use the Cells function (see the sample code below). Can you help me to copy a range of cells between worksheets, that which enables me to use the Cells function please?
Worksheets(“data”).Range(Cells(2, 4), Cells(2, 5)).Value = Worksheets(“import”).Range(Cells(2, 4), Cells(2, 5)).Value
Regards,
Tony
Hi Tony,
The Cells properties with the Range property will also need the sheet reference. Otherwise, they will reference the active sheet.
I’m planning to add a video on this. It’s a very common issue. Thanks!
This really helped me with my code.. I don’t have words to thank you.. Thanks a lot!!
Thanks Jon! You helped me too…
Really a common issue!
Thank you Jon!. This code helped me a lot!
Hi I want to copy data from one worksheet to another worksheet in same workbook. Could you please help me.
I want to enter data in “Sheet1” range (A4- J4) and it will automatically paste in sheet 2 range (A2:J2) when I click button “Done”
And data of Sheet 1 which entered earlier will automatically remove so I can enter new data in same range (A4:J4) and when click done then it will paste on below old date (A3:j3).
I want to use same range to enter data and it will store to another sheet.
Please tell me code of this.
Thanks in advance
Hi jon, Iam new to VBA topics. I always record macros in excel 2010. Right now. I want to copy the cell A1 TO A6 from one worksheet to another worksheet in any any column (say A1 to A6). Next time when i run the macros, it has to copy the same cell A1 to A6 from one worksheet to another worksheet in another column (like column wise values keeps on adding without replacing existing column). can u please help me to generate code.
hi,
i want to copy a table in workbook 1 to workbook 2 using the given example codes but when i run it, it always gives me error: Run-time error ‘9’ : Subscript out of range. How do i fix it?
Hi,
I have more than 1000 excel files.
I need to copy the first sheet from each excel file (without formula, just the values) and put in another excel file separately for each document.
Can you give me some ideas how can I do this automatically, please?
Thanks
Hi Lorand,
For this process you will need to loop through a list of the files, or all the files in a folder, open each file, copy/paste the values, then repeat for the next file. I don’t have any blog posts on this, but I do explain this process in detail in module 11 of the VBA Course, available through Elevate Excel. In this module we build an application I call the File Manager that can be used to run any macro on a set of files. I have used this tool to automate budget processes and report consolidations.
You can also use Power Query for this consolidation process. I hope that helps.
Hello
Please help me as I want four different cell value to copy from different worksheet and then want to paste it in a new worksheet of same work book.
For eg:
I have a workbook name Book1.
It has 8 worksheet with different name.
Each worksheet has an Invoice in which I need four cell value like I8, D13, I9, J43. So I need these cell value from each worksheet and then want to simply paste the data in different worksheet of same workbook
Regards
Abhishek
Don’t know if this is possible at all but this is what I am trying to do.
I have a worksheet that pulls data from calculations from another worksheet. This worksheet is then copied and pasted data only and another sheet uses this data in further calculations. What I need is when the data is copied the first time with the macro for it to also copy a formula and put it into a different sheet depending on which option is selected in a drop down menu. Can you help?
How to excel VBA code to copy and paste with the following criteria: I want to copy data on cells A1 and I want to copy as many data as in the right column. How is its excel VBA code?
this does not work:
‘Range.Copy to other workbooks
Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy _
Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”)
run time error 9 subscript out of range. please let me know if there is a solution to this. thanks
Hi Jon and others,
i have approx. 20 sheets (call them “breakdown (BD) sheets”), each starts at cell A7, but overall range of BD sheets differs, because each of them is calculated from input data (e.g. BD sheet 1 has range from A7:F20; BD sheet – A7:F60 etc.). Additionaly, after finishing this mapping (from input data) on BD sheets, i need to convert formulas in these cells to values, except Total row, as each of BD sheets ends with Total row. So i would like to ask you, if is possible to include in one macro all these conditions, i.e. different range, and only up to Total row.
thanks
I have a data like below
Name ID
A 1
A 2
C 3
C 4
C 5
I want to filter in name for eg. “A” then copy filtered data without header and paste in another sheet.
Then I want to fitler again in name “C” select filtered data and want to copy without header and paste to below of previously paste data.
Everytime my data will not be same.. Sometimes In name “A” Data will be more than 2 lines.
Kindly show how to do it by recording macro instead of writing macro.
Please help me
Thanks in advance.
I want to copy the file in range A1 to cel A8 as much data as in range A2; A10.
For example: if the data in range A2; A10 amounted to 4 then range A1 in copy to range A2 as much 4 too.
How to VBA code for it?
Hi Jon!
Good morning
Can you help me with this:
I have a VERY LONG list where i have applied subtotal for the prices. And, as the subtotal appear just below the group, i need that headings to be appeared at the top also.
For example:
CATEGORY VEHICLE PRICE
A car 12
B car 13
C car 14
car Total 39
D bike 34
R bike 56
bike Total 90
F scooter 24
H scooter 32
scooter Total 56
Grand Total 185
I want to automatically copy information in cells A3:A21 and N3:N21 from sheet1 and paste in to A2:A37 in sheet2 and avoid blank cells.
Please can you help!
Looking for a macro that would pick up the value of a cell in a column that I have selected and copy same to a specific cell in a different sheet to execute a series of calculation. A specific value from this calculation can then be displayed in the original sheet. I can then scroll to a different cell on the same column and repeat this process.
D278 -31T212-5 +215.10-4
D278 -31T212-5 +215.10-4
it should be like
D278 -31T212-5
D278 +215.10-4
Thank you John for your great work and detailed presentation. I am wondering if there is a way to copy paste data beetween different workbooks.
Keep up the good work.
Dear Sir,
I want to copy one excel file to another excel file.
Not that, I want to copy whole sheet and then paste only value.
Unable to do .. Please help me..
Thanks in advance !
Dear Rahin,
I try to do same what you want to do it exactly
so if you find the code for copy from one excel file to another
file please share it with me
am waiting your reply
thank you
Hello Jon,
I tried to apply your videos on my current issues. I want to paste the value to sheet2 not in sheet 1 – hope you can help me– Thank you
Sub test()
Dim LastRow As Long
Dim cell As Range
Dim rng As Range
‘change sheet nam teo suit
With ThisWorkbook.Worksheets(“Sheet1”)
‘find lastrow in columns A:B
LastRow = Application.Max(.Cells(.Rows.Count, “A”).End(xlUp).Row, _
.Cells(.Rows.Count, “B”).End(xlUp).Row)
‘Copy values from A:B to D:E
.Range(“D2:E” & LastRow).Value = .Range(“A1:B” & LastRow).Value
‘Worksheets(“Sheet1”).Range(“D1:E” & LastRow).Value = Worksheets(“Sheet2”).Range(“A1:B” & LastRow).Value
‘Iterates throught each cell in D:E and if it equals to “” – remember it
For Each cell In .Range(“D2:E” & LastRow)
If cell.Value = “” Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next
‘delete all empty cells (with “”)
If Not rng Is Nothing Then rng.Delete Shift:=xlUp
End With
End Sub
Hi Im Robin,
I Have a 100 more excel files with the different Employee names but the data is same i want to consolidate one excel sheet can you help me pls?
I can be of help. Let’s keep in touch via email.
Hi I do have some of your product. they are amazing,
I work with lots of data and copy and paste. do you have any magic which can be done more quick and easily
How about copying and pasting from one cell in one worksheet to another cell in another worksheet?
hello
i am new to vba and i want to make a dynamic drop down list with some enabling and disabling data.
waiting for your Response
Regards
Momna
Hi Jon,
I have multiple large worksheets each with up to 50 rows of sum totals.
I want to copy the sum totals to another sheet except that the totals need to be
pasted to a vertical column.
Is this possible?
When you paste sum totals to another sheet you have to click the clipboard icon after each and select copy values.
Will this action record in the macro?
I assume that I could filter on the 50 total rows so they are all together.
Then record a keystroke macro to copy each cell to the appropriate column cell in the second worksheet.
Once I record the macro for the first row I assume I would have to copy that routine 49 more times then go through that text and edit the cell numbers.
Does this sound right or is there an easier way?
Thank you
Hi Jim,
The extremely short answer is Yes, using
.PasteSpecial Transpose:=True
Without any code or actual info to go by, I would say that you should copy the max range of all rows and paste them, something like this:
Sheets(“Source”).Range(“A1:Z1”).Copy ‘ repalce Z with the highest letter of all rows
Sheets(“Target”).Range(“A1”).PasteSpecial Transpose:=True
Keep in mind that there are other settings for the PasteSpecial property, like
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlValues
so, do look up PasteSpecial and use those that suit your needs.
For 50 consecutive rows being pasted to 50 consecutive columns you could use a For statement… but I don’t believe it would work correctly on filtered rows.
For i = 1 to 50 ‘ if rows start at 17, For i = 17 to 66
Sheets(“Source”).Rows(i).Copy
Sheets(“Target”).Columns(i).PasteSpecial Transpose:=True
Next i
If the rows are not consecutive, but they are always the same rows to copy, then you could use an array to paste to Consecutive Columns. You need to previously load the array with your row numbers as needed.
For idx = 1 to 50
Sheets(“Source”).Rows(Array(idx)).Copy
or
Sheets(“Source”).Rows(Array(idx – 1)).Copy ‘ if the array’s index starts at 0
Sheets(“Target”).Columns(idx).PasteSpecial Transpose:=True
Next i
Cheers
Last Column copy made simple.
This is my working macro – but it is very slow, and I’m trying to utilize some of your other videos so that I can speed them up:
Sheets(“Sheet1”).Range(“B31:B32”).Copy
Sheets(“Sheet2”).Range(“AG27”).End(xlToLeft).Offset(, 1).PasteSpecial xlValues
Application.CutCopyMode = False
I’m trying to use:
Sheets(“Sheet2”).Range(“AG27”).End(xlToLeft).Offset(, 1).Value = Sheets(“Sheet1”).Range(“B31:B32”).Value
I can get it to copy the top cell “B31” but it won’t copy over the other cells. Even when I specify the range to copy to: Below does not work:
Sheets(“Sheet2”).Range(“AG27:AG28”).End(xlToLeft).Offset(, 1).Value = Sheets(“Sheet1”).Range(“B31:B32”).Value
I can do it with separate sets of code, but I would like to use this elsewhere with larger ranges of cells.
Hi,
I don’t know if you have found a solution, and actually, I don’t have a solution for the copy-paste statements. What I do have is a suggestion that might help with the processing speed. Use these two lines as shown below:
Sub YourSub()
Dim statements if any
Application.ScreenUpdating = False
… processing code here…
Application.ScreenUpdating = True
End Sub
CHEERS
Hello.
I want to create copy of a current template called Profile, which has reference to data to another sheet. When a new page is created from this template, I want the data in the reference cell to move down by 1 row (for example, A3 +1).
Code to duplicate the template.
‘Duplicate Profile sheet x times
Sub Copier()
Dim x As Integer
x = InputBox(“Enter number of times to copy Profile”)
For numtimes = 1 To x
ActiveWorkbook.Sheets(“Profile”).Copy _
After:=ActiveWorkbook.Sheets(“Profile”)
Next
End Sub
Do you have any suggestion? Thank you.
Hi Jon,
I have a list of data in Sheet 2 range[A2:A10]. I have to copy this data in Sheet1 Cell [A2] on Submit click one by one . For eg: When I click on Submit Sheet2 cell[A2] is copied on Sheet1 Cell[A2]. Again when i click on Submit Sheet2 Cell[A3] is copied in Sheet1 Cell[A2] and so on.
Please suggest on this.
Thanks
Hello,
I am looking to copy text then paste value lower in the spread sheet. When pasted it needs to offset to the next empty row.
Please help. Below is my current macro, but I need it to paste a value.
Sub FacialTissue()
Range(“$B$7:$H$7”).Copy Destination:=Range(“C” & Rows.Count).End(xlUp).Offset(1)
End Sub
Hi Jon,
Need a help on the below coding.
Rather than opening the workbook couple of time. would like to pull the data from cell E3 and E5(might be D6 etc) and paste it in Tranpose ie A1 and B1 (output)
Set bookList = Workbooks.Open(everyObj)
Range(“E3”).Copy
ThisWorkbook.Worksheets(“sheet1”).Activate
Range(“B100”).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Set bookList = Workbooks.Open(everyObj)
Range(“E5”).Copy
ThisWorkbook.Worksheets(“sheet1”).Activate
Range(“A100”).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Regards
Andro M
Hello,
I need to do an excel mapping. Copy data from column BG from sheet 1 to column A in sheet 2, sheet1.column A to Sheet2. column B, sheet1. column N to sheet2. column C.
Please help me on this.
I am really green to VBA and macros. I have been working on a 2 tab excel sheet. Sheet 1 is a row by row listing of a specialized part with all data about how it performed. Each line in that row is a different serial number. When that part has lasted it’s life cycle the technician will fill out all pertaining cells in that row that apply for that part.
The second tab is a form that I made in excel that would go with the expired part. There are 35 cells that would be filled in this form.
I would like to take the data from the cells in the row in sheet 1 and send it to the cells in the form I made in sheet 2.
There are some cells in sheet one that are never used for sheet 2.
I was thinking that using a transfer button for each row would work but I am not sure weather this would work. Sheet 1 would be used for the same part but different serial numbers. Sheet 2 would be used only when the part is being removed and returned with this form.
Any help would be so appreciated.
Hey Andrew,
Great question! There are different ways to go about this. You could use lookup formulas like VLOOKUP to pull the data into the sheet 2 form, then use VBA to make a copy of the sheet, print the sheet, export to pdf, or whatever you need to do with it.
It might be easiest to use lookup formulas to populate sheet 2, then use VBA for the rest of the process. If the layout of sheet 2 changes, it will be easier to update the formulas instead of modifying a lot of code.
I also have a free video series on the lookup formulas that will help if you are not familiar with Vlookup and Index/Match.
I hope that helps.
Sub CopyTransfer_Data()
‘Copy and Paste to another worksheets as Range
Worksheets(“State Legal”).Range(“I”).Copy Worksheets(“Analysis”).Range(“CR”)
Worksheets(“State Legal”).Range(“J”).Copy Worksheets(“Analysis”).Range(“CS”)
Worksheets(“State Legal”).Range(“U”).Copy Worksheets(“Analysis”).Range(“CU”)
Worksheets(“State Legal”).Range(“S”).Copy Worksheets(“Analysis”).Range(“CX”)
‘Copy and Paste to another worksheets as Range
Worksheets(“S.Lien-Ins”).Range(“B”).Copy Worksheets(“Analysis”).Range(“CV”)
Worksheets(“S.Lien-Ins”).Range(“C”).Copy Worksheets(“Analysis”).Range(“CW”)
Worksheets(“S.Lien-Ins”).Range(“D”).Copy Worksheets(“Analysis”).Range(“CT”)
Worksheets(“S.Lien-Ins”).Range(“A”).Copy Worksheets(“Analysis”).Range(“E”)
‘Copy and Paste to another worksheets as Range
Worksheets(“County Data”).Range(“C”).Copy Worksheets(“Analysis”).Range(“CY”)
Worksheets(“County Data”).Range(“C”).Copy Worksheets(“Analysis”).Range(“CY”)
Worksheets(“County Data”).Range(“E”).Copy Worksheets(“Analysis”).Range(“DA”)
‘Copy and Paste to another worksheets as Range
Worksheets(“Sample”).Range(“T”).Copy Worksheets(“ANalysis”).Range(“DF”)
Worksheets(“Sample”).Range(“U”).Copy Worksheets(“Analysis”).Range(“DG”)
End Sub
======
is there something wrong with my equation for VBA ?? I keep having an error message like “Run Time Error ‘9’”, Subscript out of range.
I will imagine my answer comes a bit too late, however, for the benefit of others…
“Runtime error 9 Subscript out of range” usually appears when we have mistyped the name of a sheet or the sheet we are calling does not exist.
A couple of things I noticed… OK, three…:
– If none of your worksheets are Chart sheets, you could shorten the code by using Sheets instead of Worksheets.
– For the County Data sheet, you repeated the second line, …Range(“C”).Copy … Range(“CY”)
– If you need to separate the lines for ease of readability and/or editing, try placing a space and an underscore after the word Copy, as shown below:
Worksheets(“State Legal”).Range(“I”).Copy _
Worksheets(“Analysis”).Range(“CR”)
(I usually further indent my separated lines with 4 spaces to ease identifying them. Well, I only indent 2 spaces in my regular code, so 4 I notice easily)
CHEERS
How to use if function in VBA?
Thanks for this. Very helpful.
Is there a way to create a new “tmp” directory in a specified path that the pdf’s can be dumped into? My issue is the generated pdf’s have to have a very specific file names or a specific order that they all get combined into for a single pdf. Best to do it all in tmp. Have to do this every week hence want to automate
Hello. Thanks for being available.
I have a workbook with tabs labeled 8.1.18 MN, 8.1.18 AM, 8.1.18 PM and so on for the entire month. I have a tab named Sheet1 where I have the above in a list – I used this list to rename all the tabs at once – ending at 8.31.18 PM – I would like to know how I can use this same list and import them in much the same way as the tabs, only placing the data from Sheet1 into a cell on every sheet, the cell I want the data to go is B3.
I don’t know how to have excel do this in much the same way it was able to rename all the tabs correctly using A1 to A93 on Sheet1 – can you help?
Hi, I am trying use the above to join values into the same cell. It works fine for one row, but as soon as I try with multiple rows it fails.
Any ideas?
Works: Range(“A2”).Value = Range(“A2”).Value & “” & Range(“P2”).Value
Fails: Range(“A2:A6”).Value = Range(“A2:A6”).Value & “” & Range(“P2:P6”).Value
Thank you
Hi, could someone help adjust this formula for me? I wanted to copy a cell that is 2 cells above the selected cell, and then paste it in the selected cell.
Thank you.
Hi,
I have two workbooks opened (both can be different names) and I want to copy a Range from Workbook1 SheetX (unkown/active) to Workbook2 (where I know the Sheet name)
Do you have any suggestions on how to do this?
Thanks,
Erika
you need to take work under one variable than activate the workbook and select particular sheet. You have to write code to copy paste in active workbook if you have any specific sheet than write sheet name.