VBA - Object Required

[TMUAT]

Hi There,

I am new to VBA, I have tried to write the code below. However, it says object required. I am new, but I thought I had everything in place.

I essentially want the vba to take a value from a cell which is a sheet name in another document, take a column reference which is in cell H1. Then copy and paste a set data (C5:C28) into the sheet referenced and the column referenced (e.g. B2, C2, D2, E2 etc etc).

Sub Upload()


Dim SheetRef As String


Dim CellRef As String


Dim ColRef As String


Dim ActiveWbk As Workbook
Dim ClosedWbk As Workbook


Set ActiveWbk = ActiveWorkbook


Set SheetRef = ActiveWbk.Sheets("Team Sheet Workings").Range("D2")


Set ColRef = ActiveWbk.Sheets("Team Sheet Workings").Range("H1")


Set CellRef = ActiveWbk.Sheets("Team Sheet Workings").Range(ColRef)


ActiveWbk.Sheets("Team Sheet Workings").Range("C5:C28").Copy


Set ClosedWbk = Workbook.Open("/Users/A3/Downloads/7.xlsx")


ClosedWbk.Sheets(SheetRef).Range(CellRef).PasteSpecial xlPasteValues


End Sub

Any help would be really appreciated.

Tom

 

[Joe4]

Welcome to the Board!

You use "Set" when setting objects like Workbooks, Worksheets, or Ranges.
You do NOT use "Set" when setting String, Numeric, Boolean, etc Values.

So, you would not use "Set" on any variable declared to be a String.

 

[TMUAT]

Thanks very much!!

So the code should look more like this..

Sub Upload()


Dim SR As String


Dim CellRef As String


Dim ColRef As String


Dim ActiveWbk As Workbook
Dim ClosedWbk As Workbook


Set ActiveWbk = ActiveWorkbook
Set ClosedWbk = Workbook.Open("/Users/A3/Downloads/7.xls")


SR = ActiveWbk.Sheets("Team Sheet Workings").Range("C2")


ColRef = ActiveWbk.Sheets("Team Sheet Workings").Range("H1")


CellRef = ActiveWbk.Sheets("Team Sheet Workings").Range(ColRef)


ActiveWbk.Sheets("Team Sheet Workings").Range("C5:C28").Copy


ClosedWbk.Open


ClosedWbk.Sheets(SR).Range(CellRef).PasteSpecial xlPasteValues


End Sub



However, it still does not work? Any help would be lovely!

Tom

 

[Norie]

Tom

Where in the code do you get the error?

 

[rlv01]

When posting VBA code for others to look at, it helps if you do the following:

1. Paste your VBA code into the text area
2. Highlight (select) the code
3. Cick the "#" button.
This will wrap your texts in code tags which will display it more clearly like this:

Sub Upload()
Dim SR As String
Dim CellRef As String
Dim ColRef As String
Dim ActiveWbk As Workbook
Dim ClosedWbk As Workbook

Set ActiveWbk = ActiveWorkbook
Set ClosedWbk = Workbook.Open("/Users/A3/Downloads/7.xls")

SR = ActiveWbk.Sheets("Team Sheet Workings").Range("C2")
ColRef = ActiveWbk.Sheets("Team Sheet Workings").Range("H1")
CellRef = ActiveWbk.Sheets("Team Sheet Workings").Range(ColRef)
ActiveWbk.Sheets("Team Sheet Workings").Range("C5:C28").Copy

ClosedWbk.Open
ClosedWbk.Sheets(SR).Range(CellRef).PasteSpecial xlPasteValues
End Sub

 

[TMUAT]

Apologies, will post the code as shown above.

I have played with it some more and the code works within the same sheet however it struggles to open the sheet designated and paste the data into the correct sheet and column. It stops at the Set ClosedWbk part.

[FONT=Menlo][COLOR=#011993]Sub[/COLOR] Upload()[/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] SR [/COLOR]As[COLOR=#000000] [/COLOR]String[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] CellRef [/COLOR]As[COLOR=#000000] [/COLOR]String[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] ColRef [/COLOR]As[COLOR=#000000] [/COLOR]String[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Dim[/COLOR] ActiveWbk [COLOR=#011993]As[/COLOR] Workbook[/FONT]
[FONT=Menlo][COLOR=#011993]Dim[/COLOR] ClosedWbk [COLOR=#011993]As[/COLOR] Workbook[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Set[/COLOR] ActiveWbk = ActiveWorkbook[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]SR = ActiveWbk.Sheets("Team Sheet Workings").Range("C2")[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]ColRef = ActiveWbk.Sheets("Team Sheet Workings").Range("H1")[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]ActiveWbk.Sheets("Team Sheet Workings").Range("C5:C28").Copy[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Sheets(SR).Range(ColRef).PasteSpecial xlPasteValues[/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Sub[/FONT][/COLOR]

This is the part of the code that I have working now.

Tom

 

[Norie]

Tom

Is this the problem line?

Set ClosedWbk = Workbook.Open("/Users/A3/Downloads/7.xls")

If it is I can see one definite problem and one possible problem.

The definite problem is that it should be Workbooks.Open.

The possible problem is '/Users/A3/Downloads/', which, to me anyway, doesn't look like a valid path.

Where exactly is the workbook '7.xls' located?

Is it in a folder on a network/mapped drive?