Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA code to Copy and Rename Worksheet

Hey folks,

I am fairly new to VBA coding in Excel and I am trying compile a script that will allow me to copy an existing worksheet and rename it based off of values referenced in the range A2:A54 in another worksheet.

Here is an overview:

1. Worksheet to copy - "Template"
2. Worksheet with range for renaming - "Weekly Overview"
3. Cell range for renaming - "A2:A54"
4. Values in the cells are as follows - A:2 = 1/4/10, A:3 = A2+7, A:4 = A3+7, etc.
5. The issue I am having is the fact that the values in the cells are formatted as date and have a 7 day increment (basically, each cell lists the first day of each week)
6. I would like to keep the formatting "MM-DD-YYYY" in the tab name

This is what I have so far:
--------------------
Sub Copy_Sheets()
    Dim i As Integer
    Dim wks As Worksheet
     
    Set wks = Sheets("Weekly Overview")
     
    For i = 2 To 54
        Sheets("Template").Copy After:=Sheets(3)
        ActiveSheet.Name = wks.Cells(i, 1)
        ActiveSheet.Cells(1, 2) = wks.Cells(i, 1)
    Next
End Sub
--------------------

This currently fails because of the "/" in the date format even though I have a custom format of "MM-DD-YYYY" on the cell range.

Any help with this one would be greatly appreciated.


Post your answer or comment

comments powered by Disqus
vba to copy and rename a worksheet from one .xls to another .xls...

i'm using/trying the code below--the copy works OK, but the naming does not...????

mytabname = ActiveSheet.Range("a3").Value
ws.Copy after:=wb.Sheets(wb.Sheets.Count), Name:=mytabname

thank you.

Hi everybody,

I need a VBA code to copy the values in one sheet to the other
sheet. Here I'm giving my actual problem.

I have data in columns A to M which is generated by some conditional
formulas, if the condition is satisfied it will generate real values in
the cells if the condition is unsatisfied then the cell will display
"FALSE". Now what I need is a macro that copies only the cells which
are having values and paste to the other sheet spcecified and also I
need to copy column A to Column A ...So on, but I wnat to skip some
columns like E&H.

I got a macro in this group which copy and paste values&Formats to the
other sheet by selecting the range manually and then pasting the values
at the destination by running the macro.

The macro is like this.

Sub Pastesp()
with selection
pastespecial xlvalues
pastespecial xlformats
cutcopymode=false.
end sub.

can any body helpme to expand this code so that I can apply for my
specific problem.

Thanks and Regards

Ramana

Hi everybody,

I need a VBA code to copy the values in one sheet to the other
sheet. Here I'm giving my actual problem.

I have data in columns A to M which is generated by some conditional
formulas, if the condition is satisfied it will generate real values in
the cells if the condition is unsatisfied then the cell will display
"FALSE". Now what I need is a macro that copies only the cells which
are having values and paste to the other sheet spcecified and also I
need to copy column A to Column A ...So on, but I wnat to skip some
columns like E&H.

I got a macro in this group which copy and paste values&Formats to the
other sheet by selecting the range manually and then pasting the values
at the destination by running the macro.

The macro is like this.

Sub Pastesp()
with selection
pastespecial xlvalues
pastespecial xlformats
cutcopymode=false.
end sub.

can any body helpme to expand this code so that I can apply for my
specific problem.

Thanks and Regards

Ramana

I found a code to copy and rename a sheet (I will attach the code below), but I need all the formulas from the original sheet to be copied to the new sheet. And I also need to add some make some adjustments to the formulas in the new sheet.

For example, I am going to have different rates of increase/decrease in sheet 1, and sheet 2 will be the sheet will the original data. I need to copy sheet 2, rename the new sheet, and make some changes to some cells in the new sheet using a cell from sheet 1 (different rates of increase/decrease).


	VB:
	
 NewTab() 
    Dim sName As String 
     
    sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2) 
    If sName = "" Then Exit Sub 
     
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count) 
    Sheets(Sheets.Count).Name = sName 
     
    Sheets("June").Cells.Copy 
    With Sheets(sName).Range("A1") 
        .PasteSpecial xlValues 
        .PasteSpecial xlFormats 
        .PasteSpecial xlPasteValidation 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


hi there,

need some help guys on getting a macro or vba code for copying data from one worksheet to another. on the attached file, i have mentioned on dark grey filled with bold text the constant parameters i need to copy or transfer every day on the dBase sheet next to it. can you please help me setting up a code that will work for this situation? i find it really hard to construct a vba code that will pull up the date, description, vessel type, etc. etc and paste it on the next sheet.i would like to have it on a single button that after clicking it auto populates the data i need on the other sheet on the corresponding headings. also, i want to protect the data into it to make sure that nobody can mess around with it . would appreciate any assistance. thank you very much for helping out.

I have a formatted worksheet View Format that needs to be copied to another existing worksheet View Request whenever the user makes a new request. Everything on the View Format sheet needs to be copied to the View Requested sheet. This resets (refresh) the View Requested sheet for the next user request.

I was using VBA code to delete worksheet View Requested, then create to copy of Worksheet View Format that becomes View Format (2) then rename it to View Requested.

This accomplished the refresh, but keeps incrementing the worksheet numbers in the sheet properties for View Requested sheet.

I would like VBA code that will copy everything on the View Format sheet to overlay everything on the View Requested sheet. That includes Buttons, Text, Columns, Rows, etc.

Here is the code I am using to delete and copy the existing sheets.

 
Sub Replace_View_Requested_Sheet()
'
' Replace_View_Requested_Sheet Macro
' Macro created 10/22/2010 by Jim Lombardi

'
    Sheets("View Requested Format").Select
    Sheets("View Requested Format").Copy Before:=Sheets(4)
    Application.DisplayAlerts = False
    Sheets("View Requested").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True

    Sheets("View Requested Format (2)").Select
    Sheets("View Requested Format (2)").Name = "View Requested"
    Range("A1").Select
End Sub
Here is the workbook:

Jaguar XJ8 Major Repair & Safety Issues Format WIP.xls

Look at "AT a Glance" worksheet, the option button entitled "Refreash View Requested" runs the macro "Replace_View_Requested_Sheet()". Looks like I misspelled Refresh in the option button text. Oh well, I will fix that later.

Thank you in advance for your help with this coding.

Jim Lombardi

I am looking for vba code to move data from one spreadsheet tab to another. I need it to copy based on greater than or equal to and less than or equal to a certain column. I saw other related threads with just specific values; I need a range of numbers.

Attached is an example of the before with the data download, and the after when I just manually move them over to each tab. The tabs noted GT100K or GT50K means in column I values over 100,000 or 50,000, etc.

So the code would pull based on column C or C & I from the data tab, and copy to the other tabs. After moved If you could sort the tabs by B&C too, that would be perfect. If this just pulls from the top row down, then you can ignore the prior sentence since it will be sorted before running the code.

I'm not sure if my files are working so here is kind of an brief example. If you can give me the base, maybe I could manipulate the vba for my specifics.

Example :
Account Var. Amount
45000 $105
45005 $50
56100 $25
79008 $125
67002 $25

The code would move the data (first 2 rows) in one tab for accounts 45000-56009. Then move to another tab for accounts 45000-56009 and variance amounts greater than $100 (just the first row). Move data from accounts 56000-56999 to a separate tab (copy the third row into new tab). Etc.

Let me know if you have any questions.
Thanks,
Preston

I have 2 workbooks.A Source workbook and a destination workbook. They are completely same except for 1 worksheet which has the same name in both but different data(both contain around 30 sheets). What i wanted was to Copy the rest of the identical worksheets from source workbook to destination workbook leaving that 1 worksheet which defers in data. Basically the identical worksheets present in the destination workbook should be replaced with the ones from the source workbook. The worksheets contain formulas and named ranges.
I was successfully able to write the VBA Code to copy the worksheets. But since the named ranges have a workbook scope. The named ranges still refer to locations in the source workbooks. So i get 2 named ranges with the same name. Something like

Name=VaccStart , Refers To =Sheet2!$A$2 'The one already present in the destination workbook (from the worksheet which was replaced)
Name=VaccStart , Refers To =[C:Users.....Source.xls]Sheet2!$A$2 'The one due to the copied worksheet.

I want to the named ranges to refer to the destination workbook and not the source workbook when i copy them. Since all the sheets in both the workbooks are same and im just replacing them.

I realize this has probably been covered quite a bit, but my search function isn't working right now....so bear with me if I'm a little redundant.

I'm looking to find a macro that creates a copy of a master or a template worksheet, and renames the new copy whatever the user inputs.

Any help would be greatly appreciated, thanks!

Hi Guys,

I need some help creating a Macro.

I'm trying to copy and paste rows of data that contain my criteria column A but not if column B is blank.

I want to be able to enter a team code in cell B2 in the "Summary" tab and have the Macro pull all the non-blank rows of data and paste into the "Summary" tab.

If that doesn't make sense, please let me know.

Here is my code:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long
Application.ScreenUpdating = False

If MsgBox("Update this sheet?", vbYesNo) = vbNo Then Exit Sub

Range("D3:G" & Rows.Count).Clear

For Each ws In Worksheets
    If ws.Name <> Me.Name Then
        ws.AutoFilterMode = False
        ws.Rows.AutoFilter Field:=11, Criteria1:=Range("B2").Value
        LR = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
        If LR > 2 Then
            ws.Range("A3:K" & LR).SpecialCells(xlCellTypeVisible).Copy
            Range("D3:G" & Rows.Count).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
        End If
        ws.AutoFilterMode = False
    End If
Next ws

Application.ScreenUpdating = True
Range("A3").Select
End Sub


Hey,

Thanks in advance for any help. I have a workbook in which I have multiple worksheets which are dependant on one input page. I have vba code to hide and unhide blank rows in one worksheet. What I would like to do is be able to hide corresponding rows in another worksheet. I have run into 2 problems. First, I can write code to check for blank rows, but I want it to update on every enter keystroke and this cycles through 2000 lines and takes too long. I would like to have it execute only when a cell changes. Next, the rows contain the same data, but are arranged in a different order so that row 4 on one worksheet is not the same as row 4 on the other worksheet. Any suggestions?

thanks,

Mike

Hi,

I'm currently using the following code to copy data from a range of worksheets:

Code:
For Each ws In ActiveWorkbook.Worksheets
        If ws.Name  ActiveSheet.Name Then
            ws.Range("A4:V303").Offset(0).Copy
            With Range("A65536").End(xlUp).Offset(1, 0)
                .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                              False, Transpose:=False
                .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
                              False, Transpose:=False
            End With

        End If
    Next
this code works fine, but I need to modify it so that it only copies data from specific worksheets. In this case I only want to copy data from worksheets who's name starts "Weekly Call"

Can anyone help me with modifying this code?

Regards

Mark

I need help with some code to copy a folder from one destination and paste it
to another.

Sub Transfer()

Dim copy_dir As String
Dim paste_dir As String

copy_dir = "C:test_source"
paste_dir = "C:test_destination"

FileCopy copy_dir, paste_dir

It seems that filecopy is not the answer, I need something like foldercopy.
Is there a VBA command to copy and paste folders and contents similr to
filecopy?

Thanks
Ronaldo

Would anyone have a VBA function that copies everything that's on one
Excel workbook and paste just the values to another Excel workbook? I
just want to copy the values and not the references. It would be one
sheet to another sheet.

I've looked on the newsgroup, but am not able to find anything. Any
help is much appreciated.

Thanks,
Nhien

hi,

using a textbox and a command button,
what would be the code that copies and renames a sheet when i click a button

i want it to remame it to whatever text is in the textbox

not sure if its poss.
please let me know ASAP

cheers in advance

I am trying to set up a spreadsheet to help track a budget. The worksheet titled "Purchased Items" will be filled out daily (and erased the next day for more orders) by those placing orders. My organization is divided up into separate cost centers with each having an allotted budget. I am wanting to track that annual budget by cost center in individual worksheets. I am trying to create a macro that can pull those rows with items purchased by a cost center to be moved to the appropriate worksheet. There are 3 types of orders that can be placed; "Regular", "Special", and "Emergency". On row 3, three different cost centers could have an order placed in each order type. I need the macro to only look at the range that meets the condition to be copied and pasted into the appropriate worksheet (e.g. cells A3:E3 need to be pasted into worksheet 6911 cells A3:E3. Each additional entry would be added to the next available row. Below is the macro I am attempting to make work:


	VB:
	
 cond_copy() 
    Sheets("Purchased Items").Select 
    RowCount = Cells(Cells.Rows.Count, "c").End(xlUp).Row 
    For i = 1 To RowCount 
        Range("c3:c20" & i).Select 
        check_value = ActiveCell 
        If check_value = "6911" Then 
            ActiveCell.Range("A3:E20").Copy 'I know that this is part of my problem, I don't want to copy and past the whole
range, only those rows that contain cost center "6911"
            Sheets("6911").Select 
            RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row 
            Range("a3:e3" & RowCount + 1).Select 
            ActiveSheet.Paste 
            Sheets("6911").Select 
        End If 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I limit what is copied and pasted? This macro is pasting everything into the specified worksheet if it finds "6911" in any cell.

Thanks in advance for any suggestions.

Regards,

Josh

I have a powerpoint slide deck with 42 slides. I would like to have slides 1 to 10 and 35 to 38 copied from the original file to a new presentation and slides 11-34 and 39-42 copied to another new presentation. I'm looking for VBA code to accomplish this. I'm hoping someone can help me.

Looking for some help with VBA code to do some search and calculations.

I have 4 Columns (A-B-C-D) for sake of argument. I have raw numbers in
Columns A & B and calculations in C & D. There may be anywhere from 10 to
500 Rows. Possible more. A & B may or may not be “color” filled. A with a
Lime Green and B with a Red fill.

What I need to do is search Column A from top down until I find a Green
fill. After I locate the fill, I then need to move to Column B and search
for a Red fill.

If the cell in Column B adjacent to the Green fill has a Red fill, I need to
then subtract Column B from Column A and return the results in Column C. If
there were no Red fill, the search would continue down B until a Red fill is
found and then subtract the Red cell from the Green cell and return the
results in Column D.

After subtracting the Red from the Green, the next step would be to drop one
Row and return to Column A in search of a Green fill. If nothing is found,
the search should continue down Column A until a Green fill is located. When
located, I then need to subtract the previous Red fill from Column B, Row ‘n’
from the new found Green fill Column A, Row ‘n’ and return the result in
Column D in the same Row as the Red fill.

If there is a Green “and Red fill in the same Row, the Red in Column B gets
subtracted from the Green in Column A; however, the Green would not be
subtracted from the adjacent Red. Again, the search would need to continue
down Column A until the next Green fill is located.

The search/calculations should stop once an empty cell is reached in Column
A or B.

The following is a sample/example from one of my spreadsheets. Figures in
Column C are calculated based on a Green fill in A and those in D from a Red
fill in B. (Copy/Paste did not include the color fill)

A B C D
42.43 41.35
41.72 41.05
41.35 40.82
41.16 40.56 0.81
41.48 40.88
41.64 40.35 (2.40)
42.09 41.18
42.29 41.67
42.75 42.05 0.70 (0.77)
42.53 42.22
42.63 41.67
42.82 42.15 1.30
42.75 42.07
42.46 42.00
42.38 41.85
42.40 41.64
41.98 41.52 (1.01)
42.05 41.59
42.53 41.93 2.09
42.19 41.80
42.24 41.68
42.03 40.90
41.08 40.69
41.01 40.44 (2.29)
42.04 40.88
42.11 41.64
42.45 41.93
42.73 41.32 3.41
41.62 40.60
41.05 40.28
40.99 40.32
40.65 40.26
40.94 39.32

Any help with some code would be greatly appreciated.

Thanks
SHD

--
SHD

Hey guys, I need some help.
I am trying to write some code to copy and paste values from columns in one worksheet to another, the thing is the columns are not next to each other.
I need to copy the data in each column up until the last row with entries in. This is what I have so far:

	VB:
	
Sheets("Sheets1").Select 
Range("B1:B,H1:H" & Range("H65536").End(xlUp).Row).Copy 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I get a Run-time error '1004': Method 'Range' of object '_Global' failed

I am guessing that the way I have assigned the range is wrong? Can anyone help me shed some light on this error and how to correct it?

Thanks

Okay bear with me here, I know it seems like Im trying to do excel-inception but Im not. Im relatively new to VBA so Im trying to understand if this is possible. Can I write a vba code to be run upon the user pressing a button in a sheet that has yet to be created?

Excel sheets attached to explain.

The original sheet is titled With code 1.xlsm. The code is associated with the button Click me and once the button is clicked, sheets 1 and 2 are created (click the button to see).

What I would like is for, when sheets 1 and 2 are created, for them to be created with a button Button 1 linked to another code as shown in file With code 1 and 2.xlsm which multiplies the age value by two (click the button to see)

Is this possible?

Thanks thanks ((:

Hello ladies and gentlemen

I know this is a simple solution but there is some aspects of VBA that I still can't get me head around, therefore I am now stuck.

Project Gantt_BCP.xlsm
Cells A8:A24 in Sheet "Help" is a list of items which now and again I would need to copy and paste below the last row in column B in Sheet "DailyPlan". I would like this in the form of a button, so that I click the button and hey presto the 17 items are pasted immediately in the last 17 rows.

Can anyone give me a quick detail how to do this?

Ta

Hello group,

I am trying to write a vb code to copy a field from one Excel worksheet to other Excel file but having error message as “run time error… Subscript out of range”. Here is my code:

Dim rw&, col&, cel As Range
rw = "O:AdminCheque Pick Up Forms - Requested" & Sheets("Report").[D:D].Find("").Row
col = 4
For Each cel In Range("D36")
Sheets("Report").Cells(rw, col) = cel.Value
col = col + 1
Next

End Sub

Please help in identifying the error
Thanks

Hi guys,

Im stuck on trying to create a code to search through a worksheet
trying to match data in column G, then on A and D.

So in example I have 13000 rows of data.

Where for example some of the data looks like this :

Column A Column B Column G
123 456 6
123 456 6
345 567 8
322 567 8

By the data above, I would want the macro to highlight the row 1 and 2 as they have the same data in column G, then in A and B. Row 3 and 4 are incorrect, as they do not have matching data. But even if the data that was correct matching, was in row 1 and 34, I would want them to be put together and highlighted.

I assume a quick sort of columns before the macro kicks in would fix that?!

I really appreciate any help on this, as per now its a days work for something im sure can be done in a few mins.

Dear all,

In my sheet 1 cell C3, the name of my current file is stored there.

I want to print the file to a PDF file instead of to printer.

When I print to PDF, it asks for the file name.

I have to click C3, copy the content in it (eg,IN01166), then when it asks for the file name, I paste it there.

Is there a VBA code to copy the content of the cell? so that I can paste without click the cell and copy.

I have already a long code in this file, just to add another one or two line to streamline this.

I tried the code of simply click C3 --> copy. But when I paste in the window which ask for file name, nothing can be pasted.

Thank you !


No luck finding an answer? You could always try Google.