UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Copy Of Excel File    
 
   
John_Ross
post Mar 2 2012, 03:52 PM
Post #1

UtterAccess Veteran
Posts: 332
Hi,

I want a vba code that whenever I runs it, it does the following:

1. Creates a new file of “Cities in the USA – Original Jan12.xls”
2. The New file should be renamed as “Cities in the USA – Jan12.xls”
3. The new file will only contain “Cities in US” sheet and that too the values of cells “A1:X90”
4. The new file will have only contain “values” and no formulas from the original file.
5. The new file is to be saved in the same folder as the original file.

How to?
Go to the top of the page
 
+
norie
post Mar 2 2012, 04:40 PM
Post #2

UtterAccess VIP
Posts: 4,297
So you want to copy the 'Cities in USA' worksheet from the 'Cities in the USA - Original Jan12.xls' to a new workbook?
CODE
Sub CreateNewCities()
Dim wbSource As Workbook
Dim wbCities As Worksheet
Dim wsCities As Worksheet

    Set wbSource = ThisWorkbook ' assumes code is in 'Cities in the USA – Original Jan12.xls' workbook

    wbSource.Worksheets("Cities in the USA").Copy
    
    Set wbCities = ActiveWorkbook

    Set wsCities = wbCities.Worksheets(1)

    With wsCities.Range("A1:X90")
          .Value = .Value
    End With

    wbCities.SaveAs wbSource.Path & Application.PathSeparator & "Cities in the USA - Jan12.xls"

End Sub
Go to the top of the page
 
+
John_Ross
post Mar 2 2012, 04:49 PM
Post #3

UtterAccess Veteran
Posts: 332
Hi Norie,

Thank you once again. Let's say next month when I run this code in the Feb12 file, do I have to change the code from Jan12 to Feb12. Is it posssible for it to automatically detect the Feb12 in the name?

Thanks!
Go to the top of the page
 
+
norie
post Mar 2 2012, 05:23 PM
Post #4

UtterAccess VIP
Posts: 4,297
John

I wondered about that.

If it's 2 months ago and this year you can use this.
CODE
strFileName = "Cities in USA - " & Format(DateSerial(Year(Date), Month(Date)-2, 1), "mmm yy") & ".xls"

So today that would give 'Cities in USA - Jan 12.xls'.

If it's not 2 months ago but the previous month subtract 1 from Month(Date).
Go to the top of the page
 
+
John_Ross
post Mar 2 2012, 09:50 PM
Post #5

UtterAccess Veteran
Posts: 332
Hi Norie,

I tried your code but it's giving me an error:

Compile Error: Method or Data Member Not found. I added your month code at the bottom.

CODE
Sub CreateNewCities()
Dim wbSource As Workbook
Dim wbCities As Worksheet
Dim wsCities As Worksheet

    Set wbSource = ThisWorkbook ' assumes code is in 'Cities in the USA – Original Jan12.xls' workbook

    wbSource.Worksheets("Cities in US").Copy
    
    Set wbCities = ActiveWorkbook

    Set wsCities = wbCities.Worksheets(1)

    With wsCities.Range("A1:X90")
          .Value = .Value
    End With

    wbCities.SaveAs wbSource.Path & Application.PathSeparator & "Cities in USA - " & Format(DateSerial(Year(Date), Month(Date) - 2, 1), "mmm yy") & ".xls"

End Sub
Go to the top of the page
 
+
John_Ross
post Mar 2 2012, 09:56 PM
Post #6

UtterAccess Veteran
Posts: 332
I tried the original code as well and got the same error.
Go to the top of the page
 
+
John_Ross
post Mar 5 2012, 08:41 AM
Post #7

UtterAccess Veteran
Posts: 332
Hi,

Please help. Code giving me error as above.

Thank you!
Go to the top of the page
 
+
norie
post Mar 5 2012, 10:45 AM
Post #8

UtterAccess VIP
Posts: 4,297
John

I was sure I posted a correction, must not have gone through for some reason.

The problem is that wbCities has been declared, wrongly by me, as a Worksheet when it should be a workbook.
CODE
Dim wbCitiies As Workbook
Go to the top of the page
 
+
John_Ross
post Mar 5 2012, 11:28 AM
Post #9

UtterAccess Veteran
Posts: 332
Hi Noorie,

Thank you again. I tried and the code is working but there are few problems:

1. It changes all the month values from January 2012 to some 5 digit numbers.
2. It copies the whole worksheet instead of the range.

Thanks.
Go to the top of the page
 
+
norie
post Mar 5 2012, 11:35 AM
Post #10

UtterAccess VIP
Posts: 4,297

1 Those are the real values of the dates - when you convert to values you lose formatting.

2 Do you just want to copy that range and not the whole worksheet?
I assumed it was the whole worksheet and you wanted to conver that particular range to values.
Go to the top of the page
 
+
John_Ross
post Mar 5 2012, 11:39 AM
Post #11

UtterAccess Veteran
Posts: 332
I wanted only that particular range. When I copy and paste value manually I never had the format changing but. But the month values are in the range and they chnaged to numbers.
Go to the top of the page
 
+
John_Ross
post Mar 6 2012, 02:40 PM
Post #12

UtterAccess Veteran
Posts: 332
Okay. Here what the code looks now, but there's a button that runs the macro that gets copied over to the new file. I want to delete the button in the new file. How to?

CODE
Sub WorkBookCity()
Dim wbCity As Workbook
Dim wbCityTemplate As Workbook
Dim wsCityTemplate As Worksheet

Set wbCity = ThisWorkbook
    wbCity.Worksheets("City in USA").Copy
    Set wbCityTemplate = ActiveWorkbook
    Set wsCityTemplate = wbCityTemplate.Worksheets(1)
    
    With wsCityTemplate
    .Range("A1:X91").Copy
        .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
       .Range("A92", .Cells(Rows.Count, 1)).EntireRow.Delete
       .Range("Y1", .Cells(1, Columns.Count)).EntireColumn.Delete
        wsCityTemplate.Shapes(1).Delete
        End With

wsCityTemplate.SaveAs wbCity.Path & Application.PathSeparator & "City in USA " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmmyy") & ".xls"
End Sub
Go to the top of the page
 
+
John_Ross
post Mar 6 2012, 09:21 PM
Post #13

UtterAccess Veteran
Posts: 332
It's okay. Code working great. (IMG:style_emoticons/default/thumbup.gif)

This post has been edited by John_Ross: Mar 6 2012, 09:22 PM
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 24th May 2013 - 02:32 PM