I'm stuck and I need VBA help...

[DrMingle]

I have no doubt this is basic, but I'm stuck...

Below is a recorded macro...the file "Tajuana Harper FL62.xls" is one of many Excel files I have to convert to a new file. The format's of the files are the same, but the naming convention is all over the place. In addition, the Worksheets within the files are named differently.

I simply want to replace "Tajuana Harper FL62.xls" with whatever active workbook and worksheet I choose...

My data instead gets thrown all over the place...

Below is the actual recorded macro without any enhancements...


Sub Macro9()
'
' Macro9 Macro
' Macro recorded 5/24/2009 by drmingle
'

'
Windows("Tajuana Harper FL62 Audit.xls").Activate
Range("C5:L5").Select
Selection.Copy
Windows("Team DuBose Audits.xls").Activate
Range("E824").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Windows("Tajuana Harper FL62 Audit.xls").Activate
ActiveWindow.SmallScroll Down:=18
Range("C44:L44").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Team DuBose Audits.xls").Activate
Range("B824").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Windows("Tajuana Harper FL62 Audit.xls").Activate
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Team DuBose Audits.xls").Activate
Range("A824").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A824:A832"), Type:=xlFillDefault
Range("A824:A832").Select
Windows("Tajuana Harper FL62 Audit.xls").Activate
Range("E2").Select
Selection.Copy
Windows("Team DuBose Audits.xls").Activate
Range("F824").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F824:F832"), Type:=xlFillDefault
Range("F824:F832").Select
Windows("Tajuana Harper FL62 Audit.xls").Activate
Range("B4").Select
Selection.Copy
Windows("Team DuBose Audits.xls").Activate
Range("D824").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D824832"), Type:=xlFillDefault
Range("D824832").Select
Range("C823").Select
Selection.AutoFill Destination:=Range("C823:C832"), Type:=xlFillDefault
Range("C823:C832").Select
End Sub

 

[Gavona]

I simply want to replace "Tajuana Harper FL62.xls" with whatever active workbook and worksheet I choose...

Your macro copies and pastes values from one workbook to another - which does not seem to be what you say you are trying to do.

I think you want to:
(i) Rename the sheets in your target workbook
(ii)Save the workbook with a new name
(iii)Delete the original workbook from the hard drive

Is this correct? Do you want to be prompted for the new name of each sheet/workbook?

Alternatively how does copying range C5:L5 relate to the task you have described?

Gavin

 

[DrMingle]

Gavin:

Thanks for the fast reply...

I have two issues I am trying to resolve:

1. I need to paste into a worksheet as a relative reference, not absolute. (this refers to the "Tajuana Harper FL62 Audit.xls" part of the VBA code).

2. I need to be able to copy from different file names and worksheet names - however all formats are the same in each of these unique file names and worksheet names.(this referes to the "Team DuBose Audits.xls" part of the VBA code).

 

[Gavona]

Sorry, I am not getting it!

1. I need to paste into a worksheet as a relative reference, not absolute. (this refers to the "Tajuana Harper FL62 Audit.xls" part of the VBA code).

Your code uses [red]xlPasteValues[/red] so no formulae are copied and relative addresses therefore have no meaning.

In Tajuana Harper FL62 Audit.xls C5:L5 are there formulae that you wish to retain? Do these use absolute or relative addresses? Can you give an example of one of the formulae?

2. I need to be able to copy from different file names and worksheet names - however all formats are the same in each of these unique file names and worksheet names.(this referes to the "Team DuBose Audits.xls" part of the VBA code).

Your code does not refer at all to sheet names!

Not sure if it helps but you can re-direct formulae pointing to one workbook to another workbook using Edit,Links, Change Source (also in code).

Gavin

 

[DrMingle]

Thanks for your patience...

I am only wanting values from one workbook copied and pasted to another workbook. Because of some of the formating I am having to use Paste Special for things like transpose.

There are absolute cell references coming from "Tajuana Harper FL62 Audit.xls" (which by the way has only two sheets in the workbook - Revised Audit Sheet & Revised Audit Sheet (2)). There are no formulas that need to carry over just values.

I would like elements of the information from the "Tajuana Harper FL62 Audit.xls" to be copied in the workbook "Team DuBose Audits.xls" and because I will be continually adding data to this workbook (hopefully with this Macro) I would to place the new data directly under the previoius data listed.

Rather than specific sheet names...I think I would like to use the active worksheet approach...I know that these workbooks and worksheets have different names...I don't know how else I could manage it.

Not clear what this line means, but it sounds interesting:

Not sure if it helps but you can re-direct formulae pointing to one workbook to another workbook using Edit,Links, Change Source (also in code).
***

 

[Gavona]

By the way, your code could be tidied up a bit - especially if you want to use it on other workbooks. Have a look at this:

Sub test()

Dim wbkSource As Workbook
Dim wbkDest As Workbook

Set wbkSource = Windows("Tajuana Harper FL62 Audit.xls")
Set wbkDest = Windows("Team DuBose Audits.xls")

wbkSource.ActiveSheet.Range("C5:L5").Copy
wbkDest.ActiveSheet.Range("E824").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=True

wbkSource.ActiveSheet.Range("C44:L44").Copy
wbkDest.ActiveSheet.Range("B824").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=True

............

Gavin

 

[Gavona]

So, for example, you want values from wbkSource range C5:L5 (this range will never change) copied to wbkDestination with transposition and pasted in the first blank cell after cell E824?


Gavin

 

[DrMingle]

So, for example, you want values from wbkSource range C5:L5 (this range will never change) copied to wbkDestination with transposition and pasted in the first blank cell after cell E824?

The above statement is correct...

 

[Gavona]

Hopefully thiswill get you started:

Sub test()

Dim wbkSource As Workbook
Dim wbkDest As Workbook

'Set wbkSource = Windows("Tajuana Harper FL62 Audit.xls")
'Set wbkDest = Windows("Team DuBose Audits.xls")
Set wbkSource = Windows("Source.xls").Parent
Set wbkDest = Windows("Destination.xls").Parent

wbkSource.ActiveSheet.Range("C5:L5").Copy
wbkDest.ActiveSheet.Range("E824").End(xlDown).Offset(1, 0).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=True

wbkSource.ActiveSheet.Range("C44:L44").Copy
wbkDest.ActiveSheet.Range("B824").End(xlDown).Offset(1, 0).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=True

Gavin

 

[DrMingle]

Gavin...

Thank you for your persistance. However, I am getting a run time error '9'...

I did add End Sub as I had a compile error to begin with...


Any thoughts?

 

[Gavona]

This code uses the ActiveSheet at the time you run the code as source - as I think you wanted. Also I have changed to focussing on the sheets rather than workbooks.
I think the file names may have messed you up. Make sure that the destination sheet is correctly defined for you.

If you get an error then choose to debug and note the line that is erroring out.

Sub test()

Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = ActiveSheet
Set wsDest = [red]Windows("Destination.xls").ActiveSheet[/red]

wsSource.Range("C5:L5").Copy
wsDest.Range("E824").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

wsSource.Range("C44:L44").Copy
wsDest.Range("B824").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End Sub[/code]

Gavin

 

[Gavona]

Welcome to Tek-Tips by the way!

Not sure if you need to capture the name of the workbook/worksheet that you are copying the data from, or how you avoid copying the same data twice...

Post back with your successful code, or any problems - someone will be pleased to help you learn but I am signing off for today.

Gavin

 

[DrMingle]

Gavin:

Getting a Run Time Error '1004'. Below is the actual code...

Sub AuditCull()

Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = ActiveSheet
Set wsDest = Windows("Team DuBose Audits.xls").ActiveSheet
wsSource.Range("C5:L5").Copy
wsDest.Range("E824").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
wsSource.Range("C44:L44").Copy
wsDest.Range("B824").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

End Sub


When I go to debug the section below turns yellow:
wsDest.Range("E824").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

I think we are close...

 

[CTKC]

DrMingle,

wsDest.Range("E824").End(xlDown).Offset(1, 0).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=True

What this does is goes to range E824 then it looks down the column for the next cell with data. If there is not a cell with data below E824 it goes to the last row in the Worksheet and then goes one additional cell down, which if you are already at the end of the worksheet it will cause an error. I am not sure if that was your intention with this code or not. The above code does work, but i have a feeling the issue is what I stated above.

If you want to look for the last row in Column then paste your copied data one row below that just change E824 to E1.

Tom

 

[CTKC]

If you want to look for the last row with data in Column E, then paste your copied data one row below then just change E824 to E1.

Sorry for the double post but I'm not sure how to edit

 

[DrMingle]

Double post are better than no post...

I took your advice...

The issue is now the data was inserted in the destination worksheet, but in the wrong cells...

Some data went to B814:823 and the other data went to E814:823...

I replaced both E824 and B824 with E1 and B1...

Currently there is no data below row 824...

 

[SkipVought]

If E824 already is the LAST cell containing data in this column, then your End(xlDown) is ending at row 65,536, the LAST row on the sheet and Offset(1) is trying to reference a NON-EXISTANT ROW.

TILT!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Rather, you should be referencing the FIRST cell in the range of data and TEST, using CurrentRegion.Rows.Count. If that returns ONE, then the NEXT row is the cell you want. Otherwise, use the End method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DrMingle]

Skip...

I have referenced the first cell in the range...I'm not sure what CurrentRegion.Rows.Count happens to be...

The issue I am having now is it pasting to B814:823 and E814:823....

Does that make sense?

Below is the code...

Sub AuditCull()
'
' AuditCull Macro

Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = ActiveSheet
Set wsDest = Windows("Team DuBose Audits.xls").ActiveSheet
wsSource.Range("C5:L5").Copy
wsDest.Range("E1").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
wsSource.Range("C44:L44").Copy
wsDest.Range("B1").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

End Sub

 

[DrMingle]

Would this matter...


The wsDest that I am pasting to has blanks in the column B and E in every other row....

I didn't know if the program was not understanding where the end of the column...(or more appropriately me).

B814 and E814 happen to be the first blank cells in columns B and E...with data right before and right after it...

Let me know your thoughts...