Copy from workbook to workbook wlth VBA

[rspalding]

I need help copying from 1 workbook to another. I want this code to read cell C1 for the wokbook name to open and then copy a list of cells from sheet 1 into the same cells sheet 1 in a different workbook identified by C5.

Thanks for your help,

Robert

 

[Norie]

Robert

That's the data that's needed but where is it located?

In your first post you did mention C1 and C5 but you didn't say which worksheet/workbook they were in.

I'm sure it's the workbook you want to run the code from but that's not 100% clear.

 

[Datsmart]

rspalding,

What cell in your workbook are the names for Location, Workbook name, and Sheet located.
The sample code you copied from the other thread used cells B1, B2, and B3 of the worksheet named "Main".

Also, your revised code separated the Copy/Destination line of code into two separate lines. That is why nothing is getting pasted.

Your code is only copying a single cell, A1.
Where is the List of cells you mentioned, "Copy Cell G12 to G12; J17:AF21 and so on."?

 

[rspalding]

OK. I hope I get this right this time.

From:

Location: S:\ job tickets

Workbook Name: 10544-BENNY.xls

Sheet: "Estimate" or Sheet1

Cells: C1 and say C5

To:

Location: S:\ Job Tickets

Workbook Name: Estimating Master.xls

Sheet: "Estimate" or Sheet1

Cells: C1 and say C5

This is only a few of the cells that will be copied. I hope this is what you are looking for as I sense you are getting frustrated with me.

Robert

 

[rspalding]

OK.

Location is always: S:\Job Tickets

File Name: will be in AI12

 

[Norie]

Robert

It's the worksheet name that's needed, and also confirmation that the worksheet is in the current workbook.

We could guess and post code but we would have to make some assumptions.

That might not be a problem but it might be better if we had all the information.

Is this what the data looks like?

<TABLE style="WIDTH: 356pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=473 x:str><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 11pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 width=14> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=105 x:fmla='=CHAR(CODE("A")+COLUMN()-2)'>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 85pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=113 x:fmla='=CHAR(CODE("A")+COLUMN()-2)'>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=105 x:fmla='=CHAR(CODE("A")+COLUMN()-2)'>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 102pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=136 x:fmla='=CHAR(CODE("A")+COLUMN()-2)'>D</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>From:</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>To:</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Location</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>S:\ job tickets</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Location</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>S:\ job tickets</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Workbook Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>10544-BENNY.xls</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Workbook Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Estimating Master.xls</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Sheet</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Estimate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Sheet</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Estimate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Cells</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>C1,C5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Cells</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>C1,C5</TD></TR></TBODY></TABLE>

 

[rspalding]

Norie,

Yes you are correct. B3, from workbook name will be in Estimating Master.xls cell AI12.

Cell B5 will be a range of different cells. I woluld like to make 5 different groupings of cells as there are different sections to copy and paste depending on the needs.

Robert

 

[rspalding]

John,

Ok that fixed the copy problem but I need to point it to the proper sheet as there multiple sheets in the workbook and anyone could be active. Any help there?

 

[Datsmart]

Please post the revised code you are using.

Will the worksheet always be named "Estimate"?
If not, will the target worksheet name always be listed in cell D4?

 

[rspalding]

Sub CopySales()
Dim OrigWB As String, SourceWS As String
Dim lrow As Integer, lcol As Integer
'Assign variables
OrigWB = ActiveWorkbook.Name
SourcePath = Sheets("Main").Range("B1")
SourceWB = Sheets("Main").Range("B2")
TargetWS = Sheets("Main").Range("B3")
'Open WorkBook
With Workbooks.Open(SourcePath & "\" & SourceWB)
'Assign variables from Source Workbook
SourceWS = ActiveSheet.Name
'Copy Source data to Target
Sheets(SourceWS).Range("E12").Copy Workbooks(OrigWB).Sheets(TargetWS).Range("A1")
'Close Workbook
.Close
End With
End Sub

Not always as there are hidden sheets that contains some info that i need to copy from. For these I assume it would be an un-hide command and then an active sheet command.
also, when the workbook opens that i'm retreiving, it asks about updating the wotkbook. At this point it will always be yes. Can that code be added?

Thanks,

Robert

 

[Datsmart]

What do you have in cells B1, B2, and B3 of the worksheet named "Main"?
That is where your code is picking up the name for the Path of the workbook to open. (B1)
The workbook name. (B2)
The Worksheet name to copy to. (B3)

If your data is different that what Norie posted in thread #15. Adjust the code as needed.