copy certain range to different workbook

borntobe

Board Regular
Joined
Dec 13, 2013
Messages
66
Hi,<o:p></o:p>
If a cell value is “TOTAL” in the source workbook, then I want to copy certain range to Masterworkbook.<o:p></o:p>
<o:p> </o:p>
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub CopyTotaland Paste<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Rng As Range<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][COLOR=#000000][FONT=Calibri]Dim VarColNo As Integer<o:p></o:p>[/FONT][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim IRMsheet As Worksheet<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim sourceIRMsheet As Worksheet<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim sourceIRMwb As Workbook<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Dim zz As Variant<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Na As String<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim ColNumber As Integer<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim lastIRMr As Integer<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim lastIRMc As Integer<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim IRMrng As Range<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set IRMsheet = ThisWorkbook.Sheets("ABC")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]zz = Application.GetOpenFilename(FileFilter:="ExcelFiles (*.xlsm), *.xlsm", Title:="Please select a correct projectnumber file")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]  Workbooks.OpenFilename:=zz, Na = ActiveWorkbook.Name<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Debug.Print Na<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Windows(Na).Activate<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Set sourceIABCwb =ActiveWorkbook<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set sourceABCsheet = sourceIRMwb.Sheets("ABC")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] lastABCc = sourceABCsheet.Cells(1,Columns.Count).End(xlToLeft).Column<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] lastABCr = sourceABCsheet.Cells(Rows.Count,1).End(xlUp).Row<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]For ColNumber = 1 To lastABCc<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][COLOR=#000000][B][FONT=Calibri]ABCrng =sourceIRsheet.Cells(1, ColNumber)   ‘[/FONT][/B][B][FONT=Wingdings][FONT=Wingdings]ß[/FONT][/FONT][FONT=Calibri]--object required??<o:p></o:p>[/FONT][/B][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     If ABCrng.Value ="TOTAL QTY" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        sourceABCsheet.Range(Cells(21,4), Cells(lastIABCr, ColNumber)).Copy ABCsheet.Range(Cells(21, 4), Cells(lastABCr,ColNumber))<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Next ColNumber<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
I got an error:<o:p></o:p>
‘object required’<o:p></o:p>
How can I fix this?<o:p></o:p>
Thanks<o:p></o:p>
<o:p> </o:p>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi,
You have an object variable which should be assigned with the Set keyword.

Try:

Rich (BB code):
Rich (BB code):
Rich (BB code):
For ColNumber = 1 To lastABCc
Set ABCrng = sourceIRsheet.Cells(1, ColNumber)


'Your code


Set ABCrng = Nothing
Next ColNumber



You should declare the variable as Range.

Dave
 
Last edited:
Upvote 0
I set ABCrng as Range already.
<u1:p></u1:p>
Could you expain little more?<o:p></o:p>
<u1:p></u1:p>I still got the same error; object required
Thanks<u1:p></u1:p><o:p></o:p>
 
Last edited:
Upvote 0
I set ABCrng as Range already.
<u1:p></u1:p>
Could you expain little more?<o:p></o:p>
<u1:p></u1:p>I still got the same error; object required
Thanks<u1:p></u1:p><o:p></o:p>

ABCrng does not appear to have been assigned or delclared in code posted.
Same with sourceIRsheet variable which may explain why you continue to get the error.

Rich (BB code):
Set ABCrng = sourceIRsheet.Cells(1, ColNumber)

Dave
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top