Copying cells from one spreadsheet to another with vba

David0079

New Member
Joined
Jul 26, 2007
Messages
44
Hi Gurus,

I've searched the net extensively for an answer to this question but perhaps I'm looking in the wrong places. I need to copy information from specific cells on one spreadsheet to specific cells on another. The problem is that the names of the spreadsheets will not always be consistant. Is there a way in VBA to request the user of a form to select the spreadsheets they want to copy from and too and then somehow reference these in the code?

Any help greatly appreciated?

Dave
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Maybe:

Code:
Sub David0079()
Dim x As String
Dim y As String

x = InputBox("Please Enter the Source Sheet")
y = InputBox("Please Enter the Destination Sheet")

Sheets(x).Range("A1:C2").Copy Sheets(y).Range("A1")

End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Just an added comment to John's response. I think I would specify in the input box message that "Only the sheet name" is to be entered. Otherwise it is subject to input error by some concientious user putting in 'Sheets("Sheet1")' and getting a "Subscript out of range" error when it tries to fing Sheets(x) or Sheets(y).
 

David0079

New Member
Joined
Jul 26, 2007
Messages
44
Hi Guys,

Many thanks for your help with the problem. The names of the workbooks will probably be quite convoluted so I was looking for a solution whereby the user selects the open workbooks rather than typing the whole name in because as JLGWhiz pointed out, there's too much margin for error. So far I have added two comboboxes to a form and populated them like this....

Code:
Private Sub UserForm_Initialize()
Dim wkb As Workbook

With Me.ComboBox1
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With
    
        With Me.ComboBox2
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With

End Sub

This is working well and I'm seeing a list of all open workbooks in the comboboxes. Nevertheless, I also put a button on there which when clicked will copy specific cells from the first workbook to the second and therein lies my second problem. Is my code below not working because I've referenced the workbooks rather than the sheets within those books?

Code:
Private Sub CommandButton20_Click()
Dim x As ComboBox1.Value
Dim y As ComboBox2.Value

Sheets(X).Range("C58").Copy Sheets(Y).Range("G118")

End Sub

Any ideas guys?

Dave
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The Dim statement is to declare data types, not values. Try this revised version.

Code:
Private Sub CommandButton20_Click()
Dim X As String
Dim Y As String
X = ComboBox1.Value
Y = ComboBox2.Value
Sheets(X).Range("C58").Copy Sheets(Y).Range("G118")
End Sub
Code:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top